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
Using the student and faculty tables create a select query that outputs all students for a specific advisor. Generate the execution plan, select out the explain plan . Create an

Predicate - SQL Consider the declarative sentence-a proposition-that is used to introduce this topic:  "Student S1, named Anne, is enrolled on course C1." Recall that th

Row Operators The Row operators return or reference the particular rows. ALL retains the duplicate rows in the result of a query or in an aggregate expression. The DISTINCT el

Use the MASCOT tables CREDITRS, PORDS and PAYMENTS to write SQL queries to solve the following business problems. These tables / data are available to you via the USQ Oracle server

LAWS / RULES - Dollo's Law                     :           Living organisms do exhibit evolutionary irreversibility or evolution is irreversible. Williston's Law

Subprograms The PL/SQL has two types of subprograms known as the procedures and functions that can take parameters and be invoked. As the following example represents, a subp

Question: Consider the following relations (primary keys are underlined): AUTHOR (ANo, aname, address, speciality) PUBLISHER (PNo, pname, Location) BOOK (BNo, Title, ISBN,

%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

Explicit Cursors The set of rows returned by the query can include zero, one, or multiple rows, depending on how many rows meet your search criteria. Whenever a query returns

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