3 Replies Latest reply: Sep 4, 2013 8:54 AM by Shirish V RSS

    Advanced Application Development - Summit Sporting Goods

    Noor ul Huda
      Hi brothers and sisters,

      Hope all you are fine. I have learned the basics of Oracle Forms & Reports. Now feeling to enter the level of Intermediate & Advanced Developer of Oracle Forms.

      I have also newly known about Summit Sporting Goods and have created Tables and Sequences using Summit Sporting Goods schema. Now I want to develop a complete application using Oracle Forms.

      Please let me know where could I get/ download a manual/ book having an approach towards the development of a complete application of Summit Sporting Goods?

      Or please refer me a book/ guide giving the full step-by-step advanced and excellent application development process from Normalization of Database using ER Diagrams to Quality Forms Development and then Reports.

      Thanking you,

        • 2. Re: Advanced Application Development - Summit Sporting Goods
          Try http://apex.oracle.com/pls/apex/f?p=44785:2:0::NO:RIR::
          • 3. Re: Advanced Application Development - Summit Sporting Goods
            Shirish V


            U can find the ER diagrams in this PDF----

            Oracle Developer: Build


            Volume 2 • Student Guide


            I think u can download it from Oracle.


            And here's the script....

            I have created this file from Toad directly...

            I didnt have sqlloader so worked in Toad.


            I couldnt find the table descriptions and data for s_inventory table and s_image table though...



            CREATE TABLE s_dept

            (ID  NUMBER(7) NOT NULL,

            NAME  VARCHAR2(25) NOT NULL,

            REGION_ID NUMBER(7))



            ALTER TABLE s_dept

            ADD CONSTRAINT s_dept_id_pk  primary key(id)



            CREATE TABLE s_emp(

            ID NUMBER(7) CONSTRAINT s_emp_id_pk primary key,

            LAST_NAME  VARCHAR2(25) NOT NULL,

            FIRST_NAME VARCHAR2(25),

            USERID VARCHAR2(8),

            START_DATE DATE,

            COMMENT VARCHAR2(255),

            MANAGER_ID NUMBER(7),

            TITLE VARCHAR2(25),

            DEPT_ID NUMBER(7),

            SALARY NUMBER(11,2),

            COMMISSION_PCT NUMBER(4,2))





            CREATE TABLE s_item

            (ORD_ID  NUMBER(7) NOT NULL,

            ITEM_ID  NUMBER(7) NOT NULL,


            PRICE NUMBER(11,2),

            QUANTITY NUMBER(9),






            CREATE TABLE s_ord

            (ID  NUMBER(7) CONSTRAINT s_ord_pk PRIMARY KEY,


            DATE_ORDERED DATE,

            DATE_SHIPPED DATE,

            SALES_REP_ID NUMBER(7),

            TOTAL NUMBER(11,2),

            PAYMENT_TYPE VARCHAR2(6),

            ORDER_FILLED VARCHAR2(1))





            CREATE TABLE s_product

            (ID  NUMBER(7) CONSTRAINT s_product_id_pk PRIMARY KEY,

            NAME VARCHAR2(50) NOT NULL,

            SHORT_DESC VARCHAR2(255),

            LONGTEXT_ID NUMBER(7),

            IMAGE_ID NUMBER(7),


            WHLSL_UNITS VARCHAR2(25))



            CREATE TABLE s_region

            (ID  NUMBER(7) NOT NULL,

            NAME  VARCHAR2(50) NOT NULL)



            CREATE TABLE s_title

            (TITLE  VARCHAR2(25) NOT NULL)





            INSERT ALL

            INTO s_region VALUES (1,'North America')

            INTO s_region VALUES (2,'South America')

            INTO s_region VALUES (3,'Africa / Middle East')

            INTO s_region VALUES (4,'Asia')

            INTO s_region VALUES (5,'Europe')

            SELECT * from s_region;





            INSERT ALL

            INTO s_title  values('President')

            INTO s_title  values('Sales Representative')

            INTO s_title  values('Stock Clerk')

            INTO s_title  values('VP, Administration')

            INTO s_title  values('VP, Finance')

            INTO s_title  values('VP, Operations')

            INTO s_title  values('VP, Sales')

            INTO s_title  values('Warehouse Manager')

            SELECT * from dual;




            INSERT ALL

            INTO s_customer values(202,' Oj Atheletics','81-20101','6741 Takashi Blvd',

            'Osaka',NULL,'Japan',NULL,'Poor' ,14, 4,'Customer Should Always Pay By Cash Until His Credit Rating Improves.')



            INTO s_customer values(203,'Delhi Sports','91-10351','11368 Chanakya',

            'New Delhi',NULL,'India' ,NULL ,'Good',14,4,'Customer Specializes In Baseball Equipment And Is The Largest Retailer In India.')





            INTO s_customer values(204,'Womansport','1-206-104-0103','281 King Street',

            'Seattle','Washington','USA',98101,'EXCELLENT' ,11 ,1,NULL)



            INTO s_customer values(205,'Kam’s Sporting Goods','852-3692888','15 Henessey Road','Hong Kong',NULL,NULL,NULL,'EXCELLENT',15,4,NULL)



            INTO s_customer values(206,'Sportique','33-2257201','172 Rue de Rivoli',

            'Cannes', NULL,'France', NULL,'EXCELLENT',15,5,

            'Customer specializes in Soccer. Likes to order accessories in bright colors.')



            INTO s_customer values(207,'Sweet Rock Sports', '234-603620', '6 Saint Antoine',




            INTO s_customer values(208,'Muench Sports','49-527454','435 Gruenestrasse','Stuttgart',NULL,'Germany',NULL,'GOOD', 15, 5,

            'Customer usually pays small orders by cash and large orders on credit.')



            INTO s_customer values(209,'Beisbol Si!','809-352689','789 Playa Del Mar','San Pedro de Macon’s',NULL,'Dominican Republic',NULL,

            'EXCELLENT', 11, 1,NULL)



            INTO s_customer values(210,'Futbol Sonora', '52-404562','3 Via Saguaro','Nogales',NULL,NULL,NULL,'EXCELLENT',12,2,

            'Customer is difficult to reach by phone. Try mail.')





            INTO s_customer values(211,'Kuhn’s Sports', '42-111292', '7 Modrany','Prague',NULL,'Czechoslovakia',NULL,'EXCELLENT',15,5,NULL)



            INTO s_customer values(212, 'Hamada Sport', '20-1209211' ,'57A Corniche','Alexandria',NULL, 'Egypt',NULL,'EXCELLENT', 13,3,

            'Customer orders sea and water equipment.')



            INTO s_customer values(213, 'Big John’s Sports', '1-415-555-6281' ,'4783 18th Street Emporium','San Francisco' ,'CA', 'USA',94117 ,'EXCELLENT' ,11 ,1,



            'Customer has a dependable credit record.')





            INTO s_customer values(214, 'Ojibway Retail', '1-716-555-7171', '415 Main Street','Buffalo' ,'NY', 'USA',14202 ,'POOR' ,11, 1,NULL)



            INTO s_customer values(215, 'Sporta Russia', '7-3892456', '6000 Yekatamina','St. Petersburg' ,NULL,'Russia',NULL,'POOR', 15, 5,'This customer is very



            friendly, but has difficulty paying bills.Insist upon cash.')



            select * from s_customer;








            INSERT ALL

            INTO s_item values (106 ,1 ,20108, 28 ,46 ,46)

            INTO s_item values (106 ,4 ,50273 ,22.89, 75, 75)

            INTO s_item values (106 ,5 ,50418 ,75 ,98 ,98)

            INTO s_item values (106 ,6 ,50419 ,80, 27, 27)

            INTO s_item values (106 ,2 ,20201 ,123 ,21 ,21)

            INTO s_item values (106 ,3 ,50169 ,4.29, 125, 125)

            INTO s_item values (107 ,1 ,20106 ,11, 50, 50)

            INTO s_item values (107 ,3 ,20201 ,115, 130, 130)

            INTO s_item values (107 ,5 ,30421 ,16 ,55, 55)

            INTO s_item values (107 ,4 ,30321 ,1669 ,75 ,75)

            INTO s_item values (107 ,2 ,20108 ,28 ,22 ,22)

            INTO s_item values (108 ,1 ,20510 ,9 ,9 ,9)

            INTO s_item values (108 ,6 ,41080, 35 ,50 ,50)

            INTO s_item values (108 ,7 ,41100 ,45 ,42, 42)

            INTO s_item values (108 ,5 ,32861 ,60 ,57 ,57)

            INTO s_item values (108 ,2 ,20512 ,8 ,18 ,18)

            INTO s_item values (108 ,4 ,32779 ,7 ,60, 60)

            INTO s_item values (108 ,3 ,30321 ,1669, 85, 85)

            INTO s_item values (109 ,1 ,10011 ,140, 150, 150)

            INTO s_item values (109 ,5 ,30426, 18.25 ,500, 500)

            INTO s_item values (109 ,7 ,50418 ,75 ,43 ,43)

            INTO s_item values (109 ,6 ,32861, 60, 50 ,50)

            INTO s_item values (109 ,4 ,30326 ,582 ,1500, 1500)

            INTO s_item values (109 ,2 ,10012 ,175 ,600 ,600)

            INTO s_item values (109 ,3 ,10022 ,21.95 ,300, 300)

            INTO s_item values (110 ,1 ,50273 ,22.89 ,17, 17)

            INTO s_item values (110 ,2 ,50536, 50, 23, 23)

            INTO s_item values (111 ,1 ,40421, 65 ,27, 27)

            INTO s_item values (111 ,2, 41080 ,35 ,29, 29)

            INTO s_item values (97 ,1 ,20106 ,9 ,1000 ,1000)

            INTO s_item values (97 ,2 ,303211, 500, 50, 50)

            INTO s_item values (98 ,1 ,404218, 5, 7, 7)

            INTO s_item values (99 ,1 ,20510, 9, 18, 18)

            INTO s_item values (99 ,2 ,20512, 8, 25, 25)

            INTO s_item values (99 ,3 ,50417 ,80, 53 ,53)

            INTO s_item values (99 ,4 ,50530 ,45 ,69 ,69)

            INTO s_item values (112 ,1 ,20106 ,11, 50, 50)

            select * from dual;






            INSERT ALL

            INTO s_dept values(10,'Finance',1)

            INTO s_dept values(31,'Sales',1)

            INTO s_dept values(32,'Sales',2)

            INTO s_dept values(33,'Sales',3)

            INTO s_dept values(34,'Sales',4)

            INTO s_dept values(35,'Sales',5)

            INTO s_dept values(41,'Operations',1)

            INTO s_dept values(42,'Operations',2)

            INTO s_dept values(43,'Operations',3)

            INTO s_dept values(44,'Operations',4)

            INTO s_dept values(45,'Operations',5)

            INTO s_dept values(50,'Administration',1)

            SELECT * FROM dual;






            INSERT ALL

            INTO s_emp values(1,'Velasquez','Carmen','cvelasqu','03-MAR-90',NULL,NULL,'President',50,2500,NULL)

            INTO s_emp values(2,'Ngao','LaDoris','lngao','08-MAR-90',NULL,1,'VP,Operations',41,1450,NULL)

            INTO s_emp values(3,'Nagayama','Midori','mnagayam','17-JUN-91',NULL,1,'VP,Sales',31,1400,NULL)

            INTO s_emp values(4,'Quick-To-See','Mark','mquickto','07-APR-90',NULL,1,'VP,Finance',10,1450,NULL)

            INTO s_emp values(5,'Ropeburn','Audry','aropebur','04-MAR-90',NULL,1,'VP,Administration',50,1550,NULL)

            INTO s_emp values(6,'Urguhart','Molly','murguhar','18-JAN-91',NULL,2,'Warehouse Manager',41,1200,NULL)

            INTO s_emp values(7,'Menchu','Roberta','rmenchu','14-MAY-90',NULL,2,'Warehouse Manager',42,1250,NULL)

            INTO s_emp values(8,'Biri','Ben','bbiri','07-APR-90',NULL,2,'Warehouse Manager',43,1100,NULL)

            INTO s_emp values(9,'Catchpole','Antoinette','acatchpo','09-FEB-92',NULL,2,'Warehouse Manager',44,1300,NULL)

            INTO s_emp values(10,'Havel','Marta','mhavel','27-FEB-91',NULL,2,'Warehouse Manager',45,1307,NULL)

            INTO s_emp values(11,'Magee','Colin','cmagee','14-MAY-90',NULL,3,'Sales Representative',31,1400,10)

            INTO s_emp values(12,'Giljum','Henry','hgiljum','18-JAN-92',NULL,3,'Sales Representative',32,1490,12.5)

            INTO s_emp values(13,'Sedeghi','Yasmin','ysedeghi','18-FEB-91',NULL,3,'Sales Representative',33,1515,10)

            INTO s_emp values(14,'Nguyen','Mai','mnguyen','22-JAN-92',NULL,3,'Sales Representative',34,1525,15)

            INTO s_emp values(15,'Dumas','Andre','adumas','09-OCT-91',NULL,3,'Sales Representative',35,1450,17.5)

            INTO s_emp values(16,'Maduro','Elena','emaduro','07-FEB-92',NULL,6,'Stock Clerk',41,1400,NULL)

            INTO s_emp values(17,'Smith','George','gsmith','08-MAR-90',NULL,6,'Stock Clerk',41,940,NULL)

            INTO s_emp values(18,'Nozaki','Akira','anozaki','09-FEB-91',NULL,7,'Stock Clerk',42,1200,NULL)

            INTO s_emp values(19,'Patel','Vikram','vpatel','06-AUG-91',NULL,7,'Stock Clerk',42,795,NULL)

            INTO s_emp values(20,'Newman','Chad','cnewman','21-JUL-91',NULL,8,'Stock Clerk',43,750,NULL)

            INTO s_emp values(21,'Markarian','Alexander','amarkari','26-MAY-91',NULL,8,'Stock Clerk',43,850,NULL)

            INTO s_emp values(22,'Chang','Eddie','echang','30-NOV-90',NULL,9,'Stock Clerk',44,800,NULL)

            INTO s_emp values(23,'Patel','Radha','rpatel','17-OCT-90',NULL,9,'Stock Clerk',34,795,NULL)

            INTO s_emp values(24,'Dancs','Bela','bdancs','17-MAR-91',NULL,10,'Stock Clerk',45,860,NULL)

            INTO s_emp values(25,'Schwartz','Sylvie','sschwart','09-MAY-91',NULL,10,'Stock Clerk',45,1100,NULL)

            SELECT * FROM dual;






            INSERT ALL

            INTO s_ord values(101,205,'31-AUG-92','15-SEP-92',14,8056.6,'CREDIT','Y')

            INTO s_ord values(102,206,'01-SEP-92','08-SEP-92',15,8335,'CREDIT','Y')

            INTO s_ord values(103,208,'02-SEP-92','22-SEP-92',15,377,'CASH','Y')

            INTO s_ord values(104,208,'03-SEP-92','23-SEP-92',15,32430,'CREDIT','Y')

            INTO s_ord values(105,209,'04-SEP-92','18-SEP-92',11,2722.24,'CREDIT','Y')

            INTO s_ord values(106,210,'07-SEP-92','15-SEP-92',12,15634,'CREDIT','Y')

            INTO s_ord values(107,211,'07-SEP-92','21-SEP-92',15,142171,'CREDIT','Y')

            INTO s_ord values(108,212,'07-SEP-92','10-SEP-92',13,149570,'CREDIT','Y')

            INTO s_ord values(109,213,'08-SEP-92','28-SEP-92',11,1020935,'CREDIT','Y')

            INTO s_ord values(110,214,'09-SEP-92','21-SEP-92',11,1539.13,'CASH','Y')

            INTO s_ord values(111,204,'09-SEP-92','21-SEP-92',11,2770,'CASH','Y')

            INTO s_ord values(97,201,'28-AUG-92','17-SEP-92',12,84000,'CREDIT','Y')

            INTO s_ord values(98,202,'31-AUG-92','10-SEP-92',14,595,'CASH','Y')

            INTO s_ord values(99,203,'31-AUG-92','18-SEP-92',14,7707,'CREDIT','Y')

            INTO s_ord values(112,210,'31-AUG-92','10-SEP-92',12,550,'CREDIT','Y')

            SELECT * FROM dual;




            insert all

            INTO s_product values(10011,'Boot Beginner''s','ski boot',518,1001,150,NULL)

            INTO s_product values(10012,'Ace Ski Boot','Intermediate ski boot',519,1002,200,NULL)

            INTO s_product values(10013,'Pro Ski Boot','Advanced ski boot',520,1003,410,NULL)

            INTO s_product values(10021,'Bunny Ski Pole','Beginner''s ski pole',528,1011,16.25,NULL)

            INTO s_product values(10022,'Ace Ski Pole','Intermediate ski pole',529 ,1012,21.95,NULL)

            INTO s_product values(10023,'Pro Ski Pole','Advanced ski pole', 530, 1013,40.95,NULL)

            INTO s_product values(20106,'Junior Soccer Ball','Junior soccer ball',613,NULL,11,NULL)

            INTO s_product values(20108,'World Cup Soccer Ball','World cup soccer ball',615,NULL,28,NULL)

            INTO s_product values(20201,'World Cup Net','World cup net',708,NULL,123,NULL)

            INTO s_product values(20510,'Black Hawk Knee pads','Knee Pads pair',1017,null,9,null)

            INTO s_product values(20512,'Black Hawk Elbow pads', 'Elbow Pads pair',1019,NULL,8,NULL)

            INTO s_product values(30321,'Grand Prix Bicycle','Road bicycle',828,1291,1669,null)

            INTO s_product values(30326,'Himalaya Bicycle','Mountain bicycle',833, 1296, 582,null)

            INTO s_product values(30421,'Grand Prix Bicycle Tires','Road bicycle tires',927,null,16,null)

            INTO s_product values(30426,'Himalaya Tires','Mountain bicycle tires',933,NULL, 18.25,NULL)

            INTO s_product values(30433,'New Air Pump','Tire pump', 940,null,20,null)

            INTO s_product values(32779, 'Slaker Water Bottle', 'Water bottle',1286,null,7,null)

            INTO s_product values(32861, 'Safe-T Helmet', 'Bicycle helmet', 1368, 1829, 60,null)

            INTO s_product values(40421, 'Alexeyer Lifting Bar', 'ProStraight bar,' ,928, 1381, 65,null)

            INTO s_product values(40422, 'Pro Curling Bar','Curling bar', 929, 1382, 50,null)

            SELECT * FROM DUAL;






            INSERT ALL



            INTO s_product values(41010,'Prostar 10 Pound Weight','Ten pound weight',517,NULL,8,NULL)

            INTO s_product values(41020,'Prostar 20 Pound Weight','Twenty pound weight',527,NULL,12,NULL)

            INTO s_product values(41050,'Prostar 50 Pound Weight','Fifty pound weight',557,NULL,25,NULL)

            INTO s_product values(41080,'Prostar 80 Pound Weight','Eighty pound weight',587,NULL,35,NULL)

            INTO s_product values(41100,'Prostar 100 Pound Weight','One hundred pound weight',607,NULL,45,NULL)

            INTO s_product values(50169,'Major League Baseball','Baseball',676,1119,4.29,NULL)

            INTO s_product VALUES(50273,'Chapman Helmet','Batting helmet',780,1223,22.89,NULL)

            INTO s_product VALUES(50417,'Griffey Glove',q'(Outfielder’s glove)', 924,1367,80,NULL)

            INTO s_product values(50418,'Alomar Glove',q'(Infielder’s glove)',925,1368,75,NULL)

            INTO s_product VALUES(50419,'Steinbach Glove',q'(Catcher’s glove)',926,1369,80,NULL)

            INTO s_product values(50530,'Cabrera Bat','Thirty inch bat',1037,1480,45,NULL)

            INTO s_product VALUES(50532,'Puckett Bat','Thirty-two inch bat',1039,1482,47,NULL)

            INTO s_product VALUES(50536,'Winfield Bat','Thirty-six inch bat',1043,1486,50,NULL)

            SELECT * FROM DUAL;








            aLTER TABLE S_EMP






            ALTER TABLE s_customer

            ADD CONSTRAINT s_cust_sales_rep_id foreign key(sales_rep_id) references s_emp(id)



            ALTER TABLE s_ord

            ADD CONSTRAINT s_ord_sales_rep_id foreign key(sales_rep_id) references s_emp(id)



            ALTER TABLE s_ord

            ADD CONSTRAINT s_ord_cust_id foreign key(customer_id) references s_customer(id)



            ALTER TABLE s_item

            ADD CONSTRAINT s_item_ord_id foreign key(ord_id) references s_ord(id)





            DELETE from s_item where product_id=202011;

            DELETE from s_item where product_id=303211;

            DELETE from s_item where product_id=404218;



            ALTER TABLE s_item

            ADD CONSTRAINT s_item_prod_id foreign key(product_id) references s_product(id)





            Sorry for poor indentations and formatting....


            Hope this helps you...