Can someone help me with creating the programming code for this shipping methods

Question

Can someone help me with creating the programming code for this shipping methods table? Thank you!

In project task 5, you were asked to write the stored procedures to create the enti s, attributes tie and relationships in your database. The stored procedure should have created each table in in an order that took referential integrity constraints into consideration. One possible order to creating these tables is as follows: 1. customer 2. shippingAddress 3. payment Info 4. department 5. category 6. product 7. orders S. orderItems 9. shipping Methods 10. upsells 11. crossSells Since the shippingAddress, paymentlnfo and orders tables depend on the customer table, you should create the customer table before creating these three. Same is true for the hierarchy o department to category and category to product. Once the product table has been created, you can then add the orders table followed by the orderltems and shippingMethods table. Last would be to create the upsells and crossSells tables. When creating the upsells and cross Sells tables, the order of these two doesn't matter as long as the customer, orderltems and product tables are created first.

Explanation / Answer

CREATE OR REPLACE PROCEDURE my_procedure IS
DECLEARE
table_count number := 0;
  
BEGIN
   SELECT count(*)
   into table_count
   from all_tables
   where table_name = 'CUSTOMER';
   if table_count > 0 then
       EXECUTE IMMEDIATE 'CREATE TABLE shippingAddress ( '
           || 'RankID varchar(10) NOT NULL, '
           || 'SlotTime varchar(10) NOT NULL, '
           || 'SlotDate varchar(30) NOT NULL )' ;
       EXECUTE IMMEDIATE 'CREATE TABLE paymentInfo ( '
           || 'RankID varchar(10) NOT NULL, '
           || 'SlotTime varchar(10) NOT NULL, '
           || 'SlotDate varchar(30) NOT NULL )' ;
       EXECUTE IMMEDIATE 'CREATE TABLE orders ( '
           || 'RankID varchar(10) NOT NULL, '
           || 'SlotTime varchar(10) NOT NULL, '
           || 'SlotDate varchar(30) NOT NULL )' ;
       COMMIT;
   else
       dbms_output.put_line('The table isn''t there! maybe you deleted it already? Going to create again..');
       EXECUTE IMMEDIATE 'CREATE TABLE customer ( '
           || 'RankID varchar(10) NOT NULL, '
           || 'SlotTime varchar(10) NOT NULL, '
           || 'SlotDate varchar(30) NOT NULL )' ;
       COMMIT;
   end if;
table_count = 0;
   SELECT count(*)
   into table_count
   from all_tables
   where table_name = 'DEPARTMENT';
   if table_count > 0 then
       EXECUTE IMMEDIATE 'CREATE TABLE shippingAddress ( '
           || 'RankID varchar(10) NOT NULL, '
           || 'SlotTime varchar(10) NOT NULL, '
           || 'SlotDate varchar(30) NOT NULL )' ;
       EXECUTE IMMEDIATE 'CREATE TABLE category ( '
           || 'RankID varchar(10) NOT NULL, '
           || 'SlotTime varchar(10) NOT NULL, '
           || 'SlotDate varchar(30) NOT NULL )' ;
       EXECUTE IMMEDIATE 'CREATE TABLE product ( '
           || 'RankID varchar(10) NOT NULL, '
           || 'SlotTime varchar(10) NOT NULL, '
           || 'SlotDate varchar(30) NOT NULL )' ;
       COMMIT;
   else
       dbms_output.put_line('The table isn''t there! maybe you deleted it already? Going to create again..');
       EXECUTE IMMEDIATE 'CREATE TABLE DEPARTMENT ( '
           || 'RankID varchar(10) NOT NULL, '
           || 'SlotTime varchar(10) NOT NULL, '
           || 'SlotDate varchar(30) NOT NULL )' ;
       COMMIT;
   end if;

   table_count = 0;
       SELECT count(*)
       into table_count
       from all_tables
       where table_name = 'ORDERS';
       if table_count > 0 then
           EXECUTE IMMEDIATE 'CREATE TABLE orderItems ( '
               || 'RankID varchar(10) NOT NULL, '
               || 'SlotTime varchar(10) NOT NULL, '
               || 'SlotDate varchar(30) NOT NULL )' ;
           EXECUTE IMMEDIATE 'CREATE TABLE shippingMethods ( '
               || 'shipId varchar(10) NOT NULL, '
               || 'company varchar(10) NOT NULL, '
               || 'method varchar(30) NOT NULL )'
               || 'fRate varchar(30) NOT NULL )'
               || 'rRate varchar(30) NOT NULL )'
               || 'baseWeight varchar(30) NOT NULL )';
           COMMIT;
       else
           dbms_output.put_line('The table isn''t there! maybe you deleted it already? Going to create again..');
           EXECUTE IMMEDIATE 'CREATE TABLE ORDERS ( '
               || 'RankID varchar(10) NOT NULL, '
               || 'SlotTime varchar(10) NOT NULL, '
               || 'SlotDate varchar(30) NOT NULL )' ;
           COMMIT;
       end if;

       EXECUTE IMMEDIATE 'CREATE TABLE upSells ( '
               || 'RankID varchar(10) NOT NULL, '
               || 'SlotTime varchar(10) NOT NULL, '
               || 'SlotDate varchar(30) NOT NULL )' ;
       EXECUTE IMMEDIATE 'CREATE TABLE crossSells ( '
               || 'shipId varchar(10) NOT NULL, '
               || 'company varchar(10) NOT NULL, '
               || 'method varchar(30) NOT NULL )';
       COMMIT;  

exception
when others then
dbms_output.put_line( sqlerrm);

END my_procedure;

Description :

1. Given stored procedute is created in oracle database.

2. Once above stored procedure created, Use below command to execute the same so that tables can created in system.

3. In question attributes of all the entities were not mentioned except shippingMethod so i have taken same atteributes in all the entities. you can change it as per your requirements

execute my_procedure

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.