Weeks of: November 26 and December 3, 2012
Student Name: ______
Note: Professional looking is a criterion for grading your project. That means you need to pay attention to details.
Lincoln Autos is a small car dealership located in the city of Chatoon, IL. Most of its customers are either students or households looking for a second car. Appendix A shows basic information needed in order to run the dealership.
Your Assignment
Part 1: Creating the DB Objects
1) Create a folder called FirstLastProj (where FirstLast represents your first and last names) under the root of your flash drive so that the path to the folder is something like F:\JohnDoeProj. Then, copy the given project’s files found under Project in the Assignments section of the course Website to your FirstLastProj folder. Note that all your project files must be saved in the FirstLastProj folder for the application to work.
2) Normalize the tables shown in Appendix A in order to come up with four (4) tables that are in 3NF. You need to use the template given in Appendix C of this assignment to create the design of your tables. The tables must be called: AutoEmployees, AutoSales, AutoInventory, AutoCustomers, Make sure you have added the necessary fields for creating the relationships between the tables where applicable. A copy of your tables’ design (your Appendix C) must be typed and saved as a Word file to your FirstLastProj folder under the name tablestructure.doc.
3) Based on the ScriptExample.sql file found in Appendix B of this assignment, use Notepad to create a script file called projectscript.sql that will delete any existing table with the name AutoEmployees, AutoSales, AutoInventory, or AutoCustomers from your user schema. The script should also delete any constraints associated with the tables. Then, the script will create the four (4) tables you have identified in step 2 above, and insert the data into them. Save the script to your FirstLastProj folder.
4) Run the ProjectScript.sql script in SQL Plus to create and populate the tables.
Submission: upload your FirstLastProj folder to the Illia network folder at \\L7019MSDN\test$
Part 2: Creating a form based on a template
In the following steps, you create a template form and use it to create a form that displays the data from the AutoInventory table.
Note: The Tutorial example on pages 662-666 can help you do steps 5 trough 11
5) Start OC4J Instance and the Forms Builder. Then, create a form named LincolnAutos_template and save it as LincolnAutos_template.fmb in your FirstLastProj folder.
6) Within the created form template, create a window named TEMPLATE_WINDOW, and a canvas named TEMPLATE_CANEVAS. Then, change the window title to Lincoln Autos.
7) Create a boilerplate rectangle on the left edge of the canvas, as shown in Figure-1. Fill the rectangle with a light gray color. Make sure there is no line for the boilerplate rectangle.
Figure 1: Inventory form created based on the LincolnAutos_template form
8) Import the Lincoln Autos logo (LincolnAutos.jpg) from your project folder and place it on the template canvas as shown in Figure 1.
9) Create the Return button shown on the canvas, and create a WHEN-BUTTON-PRESSED trigger that exits the current form. In Object Navigator, notice that a control block is created as a result of creating the button. Change its name to TEMPLATE_BLOCK.
10) Create a visual attribute group named TEXT_ATTRIBUTES that formats text items using an 8-point Courier New font. Specify that the text items appear on a white background.
11) Save the template form.
Note: The Tutorial example on pages 666-669 can help you do steps 12-13
12) Create a new form called Inventory.fmb that is based on the LINCOLNAUTOS_TEMPLATE form. Save the new form as Inventory.fmb in your FirstLastProj folder. Create a data block and layout based on the AUTOINVENTORY table as shown in Figure 1. Apply the TEXT_ATTRIBUTES visual attribute group to all form text items.
13) Repeat what you did in step 12 to create the sales.fmb, the employees.fmb, and the customers.fmb forms based on the LINCOLNAUTOS_TEMPLATE form. Make sure that you created the respective data blocks based on the autosales, the autoemployees, and the autocustomers tables.
Part 3: Creating the main application form
In this part, you create the main application form for the Lincoln Autos integrated database application. The main application form allows users to access sales, employees, and inventory information using the switchboard in Figure 2.
Figure 2: Lincoln Autos’ main application form
14) Create a new form called Main.fmb that will be the main application from, and save it in your project folder.
15) Create a PRE-FORM trigger that initializes a global path variable to specify the location of the project files.
16) Create a form splash screen that displays the splashauto.jpg image that is stored in the project folder. You may need to check the dimensions of the splashauto.jpg file in order to set the width and the height properties of the splash image in the Property Palette correctly.
17) Format the main form application canvas as shown in Figure 2. Use the autoimage.gif image stored in the project folder as the background image on the canvas. Then, place the Lincoln Autos logo (lincolnautos.gif) above the switchboard buttons as shown in Figure 2.
18) Create the Sales Data and Employees Data switchboard buttons for the previously created Main.fmb form as shown in Figure 2. Create the trigger for the Sales Data button so that when the user clicks the button, the form that is stored in the sales.fmx file in the project folder opens and displays current sales information. Create the trigger for the Employees Data button so that when the user clicks the button, the form that is stored in the employees.fmx file in the project folder opens and displays employees’ information. Use the global path variable in all commands. Save the modified form.
19) In this step you create a report object in the previously created Main.fmb form. If not already done, create the View Inventory switchboard button shown in Figure 2. Then create a trigger for the View Inventory Report button so that when the user clicks the button, the report object appears in a browser window. Note that because you do not have the local report server installed on your computer this will not work. But you need to do it anyway. Make sure to use the global path variable to specify the location of the report filename and the report output filename, and also to display the report output in the browser window. See Figure 8-10 in the Oracle book for the sample code used in a previous assignment. Save the modified form.
Part 4: Creating the application’s pull-down menu
In this part, you create a new menu module that displays the pull-down menu selections for the Lincoln Autos integrated database system. Figure 3 shows the menu design.
Figure 3: Menu design
20) Create the menu shown in Figure 3. The menu should have all the access keys and all the necessary triggers. You must attach the menu to the main.fmb form so that when the main form is run that menu is displayed instead of the default menu.
21) Make sure that when the user selects a menu item from the Sales & Employees menu, the application menu created in step 19 is displayed. But when the user selects a menu item from the Database Maintenance menu, the default form menu must be displayed. This is where you need the DO_REPLACE and NO_REPLACE option for your CALL_FORM procedure.
Submission: upload your FirstLastProj folder to the Illia network folder at \\L7019MSDN\test$
Appendix A
Table 1
Sale_ID / SaleDate / Inventory_ID / Make / Model / Body / Engine / TransS001 / 1/1/2011 / IV005 / Toyota / Camry SE / Sedan / V4 / Automatic
S002 / 2/1/2011 / IV009 / Chevy / Impala XE / Sedan / V4 / Automatic
S003 / 2/3/2011 / IV001 / Honda / Accord LE / Sedan / V4 / Manual
S004 / 1/7/2011 / IV010 / Chrysler / 300M / Sedan / V4 / Automatic
S005 / 3/1/2011 / IV016 / Toyota / Camry XE / Sedan / V6 / Automatic
S006 / 1/28/2011 / IV003 / Ford / Escape-i / SUV / V6 / Manual
S007 / 1/20/2011 / IV004 / Ford / Explorer / SUV / V8 / Automatic
Table 1 (continued)
Year / Cost / SalePrice / CustomerName / CustomerAddress / CustomerPhone2009 / 8500 / 10250 / John Knew / 200 Lincoln Ave, Chatoon IL / 2173451111
2010 / 16000 / 21000 / Isabel Williams / 234 Elm Street, Worth IL / 2177818569
2008 / 5000 / 8500 / George Busch / 128 Mckinley Ave, Salem KY / 3255669000
2005 / 5200 / 9000 / Elaine Wong / 41 Dawn Street, Rardin, IL / 2175420010
2003 / 4000 / 6000 / Steve Simpson / 210 10th Street, Chatoon, IL / 2177453210
2010 / 11000 / 15000 / Thether Saw / 102 Ohio Rd, Decatur, IL / 2179504566
2011 / 24000 / 28000 / Lisa Bloom / 74 Indian Road, Chester, KY / 4109581000
**********************************************************************************
Table 2
Employee_ID / FirstName / LastName / Title / ExperienceE001 / John / Longhorn / Salesperson / 10
E002 / Audry / Brown / Secretary / 5
E003 / Luc / Jambon / Salesperson / 5
E005 / Ken / Williams / Mecanic / 5
E006 / Diane / Lawson / Salesperson / 3
E007 / Roberto / Gonzales / Salesperson / 5
E008 / Steve / Johns / Manager / 10
Table 2 (continued)
Address / Phone102 Ohio Street, Chatoon, IL / 2175824102
14 Lerna Road, Chatoon, IL / 2178542100
120 Jacki Lane, Chatoon, IL / 2178542121
128 Lincoln Avenue, Chatoon, IL / 2175242100
100 Main Street, Salem, IN / 2178754100
145 Neil Avenue, Champaign, Il / 2173251000
123 Prospect Avenue, Urbana, IL / 2176251015
Appendix B: scriptExample.sql
Appendix C: tables’ structure
Table’s name: ______
Column / Data type / Field size / Format / Default valueNote: The table’s primary key must be bold and underlined. The foreign key (if any) must be listed last and must be bold.
Table’s name: ______
Column / Data type / Field size / Format / Default valueNote: The table’s primary key must be bold and underlined. The foreign key (if any) must be listed last and must be bold.
Table’s name: ______
Column / Data type / Field size / Format / Default valueNote: The table’s primary key must be bold and underlined. The foreign key (if any) must be listed last and must be bold.
Table’s name: ______
Column / Data type / Field size / Format / Default valueNote: The table’s primary key must be bold and underlined. The foreign key (if any) must be listed last and must be bold.
OraProjectF12.doc 4/8