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
Joining in SQL Joining IS_CALLED and IS_ENROLLED_ON in SQL SELECT * FROM IS_CALLED NATURAL JOIN IS_ENROLLED_ON This is an example of an SQL table expression. I have been

Using Subqueries A subquery is a query (typically enclosed by parentheses) that appears within another SQL data manipulation statement. If evaluated, the subquery gives a va

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

Disjunction (OR, ∨) Again we have nine rows instead of just four and again, when unknown is not involved, the rows are as for 2VL. Also, when anything is paired with true, t

Magento change address format depending on store Project Description: What I need is that depending on the store in which the customer bought the address should change the fo

Expression: This is a randomly complex combination of constants, variables, literals, operators, & function calls. The simplest expression consists of a single variable. If th

Using Aliases The Select-list items fetched from a cursor related with the %ROWTYPE should have simple names or, if they are expressions, should have aliases. In the example bel

Inner Join We have learned how to retrieve data from one table by using SELECT statement. But, as we have learned, normalized relational databases mean the data is spread betw

Example of NOT EXISTS in SQL Example: Use of NOT EXISTS CREATE ASSERTION Must_be_enrolled_to_take_exam_alternative1 CHECK ( NOT EXISTS (SELECT StudentId, CourseId

Assigning and Comparing Collections One collection can be assigned to other by an SELECT, INSERT, UPDATE, or FETCH statement, an assignment statement, or by a subprogram call. A