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
PITS Depressions in secondary cell wall is called pit. A pit present on the free cell wall surface without its partner is called Blind pit. It consists of 2 parts -

I need SQL , WP SQL Expert Project Description: Expert required to modify WP SQL query. Skills required are MySQL, SQL, PHP, Wordpress

User-Defined Exceptions The PL/SQL defines the exceptions of your own. Dissimilar to the predefined exceptions, the user-defined exceptions should be declared and should be rai

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

Declaring Cursor Variables Once a REF CURSOR type is define by you, and then you can declare the cursor variables of that type in any PL/SQL block or subprogram. In the exampl

Need Windows and Linux system Administrator We are seeking a part time system administrator to take care of our servers. Your things to do would add, but not limited to: -

Effects of NULL in Aggregate Operator - SQL Let aggop(x) be an invocation of some aggregate operator aggop in SQL, where x is an expression (usually an open expression) to be

Transaction Visibility As the figure shows, the changes made by an autonomous transaction become visible to another transaction whenever the autonomous transaction commits. Th

Fetching with a Cursor The FETCH statements retrieve the rows in the result set one at a time. After each and every fetch, the cursor advance to the next row in the result set

Providing Results of Queries Expressing queries in SQL is the (big) subject. Here I present just a simple example to give you the flavour of things to come in those chapters.