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 Objects1) 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$

document.doc 1/8

Developing the Lincoln Autos

database application

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 115) 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-1312) 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

document.doc 2/8

Template form objects

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.

document.doc 3/8

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$

document.doc 4/8

Sales & Employees Database Maintenance Reports HelpSales Info (sales.fmb) Customer (customers.fmb) Inventory (inventory.rdf) Help TopicsEmployees (employees.fmb) Sales (sales.fmb) Sales (not implemented) ---------------Exit Inventory (inventory.fmb) About

Appendix ATable 1

Sale_ID SaleDate Inventory_ID Make Model Body Engine TransS001 1/1/2011 IV005 Toyota Camry SE Sedan V4 AutomaticS002 2/1/2011 IV009 Chevy Impala XE Sedan V4 AutomaticS003 2/3/2011 IV001 Honda Accord LE Sedan V4 ManualS004 1/7/2011 IV010 Chrysler 300M Sedan V4 AutomaticS005 3/1/2011 IV016 Toyota Camry XE Sedan V6 AutomaticS006 1/28/2011 IV003 Ford Escape-i SUV V6 ManualS007 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 21734511112010 16000 21000 Isabel Williams 234 Elm Street, Worth IL 21778185692008 5000 8500 George Busch 128 Mckinley Ave, Salem KY 32556690002005 5200 9000 Elaine Wong 41 Dawn Street, Rardin, IL 21754200102003 4000 6000 Steve Simpson 210 10th Street, Chatoon, IL 21774532102010 11000 15000 Thether Saw 102 Ohio Rd, Decatur, IL 21795045662011 24000 28000 Lisa Bloom 74 Indian Road, Chester, KY 4109581000

**********************************************************************************

Table 2Employee_ID FirstName LastName Title ExperienceE001 John Longhorn Salesperson 10E002 Audry Brown Secretary 5E003 Luc Jambon Salesperson 5E005 Ken Williams Mecanic 5E006 Diane Lawson Salesperson 3E007 Roberto Gonzales Salesperson 5E008 Steve Johns Manager 10

Table 2 (continued)Address Phone102 Ohio Street, Chatoon, IL 217582410214 Lerna Road, Chatoon, IL 2178542100120 Jacki Lane, Chatoon, IL 2178542121128 Lincoln Avenue, Chatoon, IL 2175242100100 Main Street, Salem, IN 2178754100145 Neil Avenue, Champaign, Il 2173251000123 Prospect Avenue, Urbana, IL 2176251015

document.doc 5/8

Appendix B : scriptExample.sql

document.doc 6/8

--script to create Clearwater Traders database-- revised 8/17/02 JM-- modified 3/27/2012 AI

DROP TABLE item CASCADE CONSTRAINTS;DROP TABLE category CASCADE CONSTRAINTS;DROP TABLE orders CASCADE CONSTRAINTS;DROP TABLE order_source CASCADE CONSTRAINTS;DROP TABLE customer CASCADE CONSTRAINTS;

CREATE TABLE customer(c_id NUMBER(5), c_last VARCHAR2(30),c_first VARCHAR2(30),c_mi CHAR(1),c_birthdate DATE,c_address VARCHAR2(30),c_city VARCHAR2(30),c_state CHAR(2),c_zip VARCHAR2(10),c_dphone VARCHAR2(10),c_ephone VARCHAR2(10),c_userid VARCHAR2(50),c_password VARCHAR2(15),CONSTRAINT customer_c_id_pk PRIMARY KEY (c_id));

CREATE TABLE order_source(os_id NUMBER(3),os_desc VARCHAR2(30),CONSTRAINT order_source_os_id_pk PRIMARY KEY(os_id));

CREATE TABLE orders(o_id NUMBER(8), o_date DATE,o_methpmt VARCHAR2(10),c_id NUMBER(5),os_id NUMBER(3),CONSTRAINT orders_o_id_pk PRIMARY KEY (o_id),CONSTRAINT orders_c_id_fk FOREIGN KEY (c_id) REFERENCES customer(c_id),CONSTRAINT orders_os_id_fk FOREIGN KEY (os_id) REFERENCES order_source(os_id));

CREATE TABLE category(cat_id NUMBER(2),cat_desc VARCHAR2(20),CONSTRAINT category_cat_id_pk PRIMARY KEY (cat_id));

CREATE TABLE item(item_id NUMBER(8),item_desc VARCHAR2(30),cat_id NUMBER(2),item_image BLOB,CONSTRAINT item_item_id_pk PRIMARY KEY (item_id),CONSTRAINT item_cat_id_fk FOREIGN KEY (cat_id) REFERENCES category(cat_id));

document.doc 7/8

------------------- inserting records into CUSTOMERINSERT INTO CUSTOMER VALUES(1, 'Graham', 'Neal', 'R', to_date('12/10/1967', 'mm/dd/yyyy'), '9815 Circle Dr.', 'Tallahassee', 'FL', '32308', '9045551897', '904558599','grahamn', 'barbiecar');INSERT INTO CUSTOMER VALUES(2, 'Sanchez', 'Myra', 'T', to_date('08/14/1958', 'mm/dd/yyyy'), '172 Alto Park', 'Seattle', 'WA','42180', '4185551791', '4185556643', 'sanchezmt', 'qwert5');INSERT INTO CUSTOMER VALUES(3, 'Smith', 'Lisa', 'M', to_date('04/12/1960', 'mm/dd/yyyy'), '850 East Main', 'Santa Ana', 'CA', '51875', '3075557841', '3075559852', 'smithlm', 'joshua5');INSERT INTO CUSTOMER VALUES(4, 'Phelp', 'Paul', NULL, to_date('01/18/1981', 'mm/dd/yyyy'), '994 Kirkman Rd.', 'Northpoint', 'NY', '11795', '4825554788', '4825558219','phelpp', 'hold98er');INSERT INTO CUSTOMER VALUES(5, 'Lewis', 'Sheila', 'A', to_date('08/30/1978', 'mm/dd/yyyy'), '195 College Blvd.', 'Newton', 'GA', '37812', '3525554972', '3525551811', 'lewissa', '125pass');INSERT INTO CUSTOMER VALUES(6, 'James', 'Thomas', 'E', to_date('06/01/1973', 'mm/dd/yyyy'), '348 Rice Lane', 'Radcliff', 'WY', '87195', '7615553485', '7615553319', 'jamest', 'nok$tell');

------------------ inserting records into ORDER_SOURCEINSERT INTO order_source VALUES (1, 'Winter 2005');INSERT INTO order_source VALUES (2, 'Spring 2006');INSERT INTO order_source VALUES (3, 'Summer 2006');INSERT INTO order_source VALUES (4, 'Outdoor 2006');INSERT INTO order_source VALUES (5, 'Children''s 2006');INSERT INTO order_source VALUES (6, 'Web Site');

--------------------- inserting records into ordersINSERT INTO orders VALUES (1, TO_DATE('05/29/2006', 'MM/DD/YYYY'), 'CC', 1, 2);INSERT INTO orders VALUES (2, TO_DATE('05/29/2006', 'MM/DD/YYYY'), 'CC', 5, 6);INSERT INTO orders VALUES (3, TO_DATE('05/31/2006', 'MM/DD/YYYY'), 'CHECK', 2, 2);INSERT INTO orders VALUES (4, TO_DATE('05/31/2006', 'MM/DD/YYYY'), 'CC', 3, 3);INSERT INTO orders VALUES (5, TO_DATE('06/01/2006', 'MM/DD/YYYY'), 'CC', 4, 6);INSERT INTO orders VALUES (6, TO_DATE('06/01/2006', 'MM/DD/YYYY'), 'CC', 4, 3);

------------------------ inserting records into CATEGORYINSERT INTO category VALUES (1, 'Women''s Clothing');INSERT INTO category VALUES (2, 'Children''s Clothing');INSERT INTO category VALUES (3, 'Men''s Clothing');INSERT INTO category VALUES (4, 'Outdoor Gear');

------------------------- inserting records into ITEMINSERT INTO item VALUES (1, 'Men''s Expedition Parka', 3, EMPTY_BLOB());INSERT INTO item VALUES (2, '3-Season Tent', 4, EMPTY_BLOB());INSERT INTO item VALUES (3, 'Women''s Hiking Shorts', 1, EMPTY_BLOB());INSERT INTO item VALUES (4, 'Women''s Fleece Pullover', 1, EMPTY_BLOB());INSERT INTO item VALUES (5, 'Children''s Beachcomber Sandals', 2, EMPTY_BLOB());INSERT INTO item VALUES (6, 'Boy''s Surf Shorts', 2, EMPTY_BLOB());INSERT INTO item VALUES (7, 'Girl''s Soccer Tee', 2, EMPTY_BLOB());

COMMIT;

Appendix C: tables’ structure Table’s name: _________________________ Column Data type Field size Format Default value

Note: 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 value

Note: 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 value

Note: 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 value

Note: The table’s primary key must be bold and underlined. The foreign key (if any) must be listed last and must be bold.

document.doc 8/8