IS 676 University of Maryland Information Integration Using Dynamic SQL Exercise

Assignment is attached.

UMBC – Department of Information Systems
IS 676: Information Integration
Homework 4: Using Dynamic SQL
We have already experienced integration of local databases using a metadata/integration layer on top of
the local DBs. This homework is to address another aspect of dynamically generating programs based on
the integration needs, namely, the automated generation of tables without them being typed by a person.
Some background information:
We have already studied from our textbook and the slides that in order to get information from multiple
repositories and stitch it together, we must know the semantics of each repository. This is the first step
towards the right direction. We definitely need more to actually accomplish the goal of integration. The
next step is to be able to generate adaptive software as the textbook refers to it, or ‘software on the fly’.
What is the software on the fly? A way to automatically generate software at run time, without the need
for developers.
Metadata Layer
Local DB
Local DB
Local DB
Results from queries
Is there a way for the software to change itself and become adaptive? The answer is yes. Within the scope
of databases (we chose to integrate databases as we all have experience working with databases. In
addition, the idea is the same beyond databases). So how do we do it? As we see in the Figure above, we
have local repositories to be integrated (Local DBs) and we have collected all semantic information and
mappings in the metadata layer above the local DBs. A high level manager in charge of integrating the
local DBs, asks a global query at the metadata layer using canonical terms (as we did in HW3) and expects
1
an integrated answer that contains consolidated information from each local DB. This is accomplished in
a number of steps outlined below (see the figure as well):
1. The manager types the query in canonical terms
2. Prepare local queries: The Integration System accepts the query and it must translate it into
separate queries, one for each local DB. In order to do so, it must consult the metadata layer
(metadata table in our course) to find the appropriate mapping for each term of the canonical
query and translate it into the term of each local DB using the information stored in the metadata
layer. As you can tell, this is being done by generating strings that contain the canonical query
but using the local DB terms (one string per local DB). It is accomplished using Dynamic SQL to
put together a string that contains the query for each local DB.
3. Execute local queries at local DBs: Once the strings are finalized, the Integration System will
perform an EXECUTE IMMEDIATE to each local DB to execute the local version of the canonical
query.
4. Collect results from local DBs: The execution of local queries will yield results that must be
collected together in temporary tables. These tables are generated using Dynamic SQL and they
contain the results from local queries.
5. Consolidate results: This is the final step where the results from the local DBs are put together,
typically with a UNION operation, or a JOIN operation and the final result is sent to the manager
who originated the initial query in canonical terms. The temporary tables are deleted.
Instructions for the homework:
First, make sure you have studied the Background Information before continuing further into the HW.
Using Dynamic SQL is quite common in integration tasks and it is quite useful as well. In this last homework
we are going to demonstrate exactly that: Automatically generate adhoc tables that are needed for
integration. The way to do it is to create a stored procedure, which when executed will create a new table
with the necessary information passed as parameters to it. This procedure can be used to create any table
with any number of columns.
1. [50 pts] Create a stored procedure called GENERATE_NEW_TABLE in the Oracle database. This
procedure requires as input parameters the following strings:
a. Name of the table to be generated (for example, ‘TEMP_PRODS’)
b. Another string with the names of columns and their datatypes for the new table. For
example: ‘ID number, product varchar2(50), quantity number’ HELPFUL HINT: You will
want to generate a string that contains a single quote at the end. For this, you need to
type two single quotes at the end
c. Include a screen-shot showing the body of your procedure and that it compiles without
errors
2. [20 pts] Create an anonymous PL/SQL block that does the following:
a. Calls the GENERATE_NEW_TABLE procedure to create a TEMP_PRODS table.
2
b. Include a screenshot with the result of ‘SELECT * FROM TABS’ command that shows that
the to be created table actually exists
3. [30 pts] Create a second anonymous program to:
a. insert 3 products of your choice in the TEMP_PRODS table
b. Write a SELECT … INTO… command to count the number products in the table
c. Print the number of products in the TEMP_PRODS table
d. Include a screenshot showing that this code works
Upload:
1. A Word file or a pdf file and upload it on Blackboard. It must contain the answers to the three
problems above.
2. The script (make sure it is a text file, not a Word file) that contains all the SQL commands
3