Design a script and integrate procedures, PL-SQL Programming

Initial thought process: Design a script which was simple and user friendly. Integrate procedures/functions to extract data under the hood. I focused on giving the user the option to retrieve information based on a time period, meaning using start date and end date to define a search constraint. For example, if a user wants information from 1-MAR-12 to 8-MAR-12, he or she can plug those dates in as search parameters.

Queries: I designed the queries to answer the project questions.

Scripts: pop_fact_table.sql, which means populate fact table, receives 2 parameters type, DATE, uses the dateas conditions within a select statement to create a cursor.These parameters are used to set a time period boundary. Also the order_items idsare extracted because they are unique and I used them as a condition to get theoi.movie_id, dml.distributor_id, store_id, o.total_payment, o.tax, number_of_itemsdata from multiple tables. I used order_completed_date because I felt it was importantto extract information for orders completed instead of just using the ordering date.Ordering date does not mean the order was completed. Also I fed this date intoa function which could build my time dimension table and return the time_id to insert into the facts table. Finally, the information gathered from order_items were inserted in the facts table.

• Fill_time_dim.sql receives a parameter type, DATE, from pop_fact_table.sql, analyzes the date, then determines the month, quarter and year in which it belongs. Finally it uses this data and a generated time_id from sequence to populate the time dimension table.

• Star_schema_table.sl drops and creates the shell for the 4 dimensions tables and the fact table.

• Add_constraints.sql sets the 4 dimension primary keys as primary and foreign keys on the facts table.

• User_interface.sql is used to simulate a user's terminal requesting information. Initially, it builds the table shells and adds constraints. Once the user determines his/her search time period, data is etracted from the operational database and is placed in the facts table.

Posted Date: 3/12/2013 2:11:20 AM | Location : United States







Related Discussions:- Design a script and integrate procedures, Assignment Help, Ask Question on Design a script and integrate procedures, Get Answer, Expert's Help, Design a script and integrate procedures Discussions

Write discussion on Design a script and integrate procedures
Your posts are moderated
Related Questions
%TYPE Attribute The %TYPE attribute gives the datatype of a record, field, nested table, database column, or the variable. You can use the %TYPE attribute as the datatype speci

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

Example of EXCEPT Operator - SQL Example, like its counterpart in the theory book, illustrates the convenience of allowing any table expression to be the source for an INSERT

Create a procedure named STATUS_SHIP_SP that allows a company to employee in the Shipping Department to update the status of an order to add shipping information. The BB_BASKETSTAT

IS NULL Operator The IS NULL operator returns the Boolean value TRUE whenever its operand is null or FALSE if it is not null. The comparisons including the nulls always yield NU

Parameter and Keyword Description: label_name: This is an undeclared identifier which optionally labels a loop. When used, the label_name should be enclosed by double ang

Aggregate Operators SQL Supports all of the aggregate operators mentioned in the theory book and many more besides. The syntax, however, involves an unusual trick that SQL cal

a. Write an anonymous block that contains a PL/SQL function. Given an order number orderNo, the function will calculate the total number of the parts in the order. Then the anonym

Obtaining a natural join by specifying the common columns Synatax: SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON USING ( StudentId ) However, a named columns join doe

Write an anonymous block that contains a PL/SQL procedure. The procedure takes two input parameter: oldZip and newZip, and it updates the zipcodes table by replacing all oldZip wit