Canonical representation of the integration layer

Assignment Help Data Structure & Algorithms
Reference no: EM131146340

Retrieving Metadata Information

You have already created a metadata integration layer on top of two databases, localDB1 and localDB2.

The purpose of this homework is to go further and retrieve information from that integrationlayer.

In such an environment, there are two types of users:

- local users who access the local database directly,by issuing queries to the local schema. These are called local queries.

- global users who access both databases by issuing queries to a global (integration) layer which in turn, accesses both databases at the same time. The queries are called global (or canonical) queries since they use terms from the canonical representation.

In this homework we are going to exploit the information stored in the metadata layer to get metadata information about canonical queries (see above).

Below is an example of a very simplistic table that could be used as a metadata table to describe metadata information about data in the two participating databases (local DB 1 and local DB 2).

Canonical Representation

Column name used  in local DB1

Data Type in local DB1

Column name used  in local DB2

Data Type in local DB2

Customer

Client

Varchar2(20)

Patron

Char (50)

Product_ID

CD_ID

Varchar2(10)

CD

number

...

...

 

...

 

A manager wants to get some information from the participating databases and submit a canonical query which is written in terms of the canonical representation of the integration layer.

Create a Word file or a pdf file and upload it on Blackboard. It should contain the answers to the problem described next.

Use the manager's questions provided in page 2 (they are also part of HW2) and submit them as canonical queries; in addition, submit queries against the metadata (integration layer) to extract information about every field in each query. In other words, for each manager question perform the following steps:

1. Submit each question as a canonicalquery: Translate each manager question as a SQL query using the canonical names (the values under the canonical field in the metadata table). This is a manual step. You need to type the query yourself. Make sure it is formed using fields which are values of the canonical representation column in your metadata table. Do this for each one of the 5manager questions (queries).

a. Example: Look at the sample table above. Assume that a query for localDB1 is: select CD_ID from CD_TABLE where Client = ‘Joe';

b. Canonical representation:The above is a local query. It is translated into a canonical query as follows: select Product_ID from Products where Customer = ‘Joe' and Product_Type = ‘CD'[NOTE: You type this queryin the deliverable manually based on information in your metadata table. All the column names and table names are canonical representation terms. Do not execute the query in Oracle, it will not run, it will give you syntax errors]

2. For each field name and table name appearing in the canonical query, type, submit, and execute another query against your integration layer and show its results to discover the corresponding names/types/etc. of that field name in canonical representation, local DB1 representation, and local DB2 representation. Print a screen shot of the query running against the integration layer and show its results. Show the canonical representation of each field, the corresponding field in localDB1 and its data type (if applicable), the corresponding field in localDB2 and its data type (if applicable), and any function (e.g. CONCAT (Street, City)) that may be needed for conversion from canonical to local.

a. Example: The canonical query in the previous question contains several column and table names (Product_ID, Products, Customer, Product_Type).

b. The first column is Product_ID. For this data item, you need to submit aquery similar to the one below, against your metadata table, and display the result: SELECT canonicalRepresentation, columnLocalDB1, datatypeDB1, columnLocalDB2, datatypeDB2, functionApplicable FROM metadata_table WHERE CanonicalRepresentation = ‘Product_ID';Continue issuing additional queries, for each one of the remaining fields of the query at hand (for this particular example the remaining fields are Products, Customer, Product_Type).

Perform the above steps (1 and 2) for each one of the manager questionsManager questions (queries)
1. List all details of a specific product given its unique identifier
2. List each product(s) of a particular author or artist
3. List each product with at least quantity N
4. List each product that is out of stock
5. List the location of the warehouse(s) which stock a product of a given unique identifier

IMPORTANT NOTE 1: You are not retrieving answers to the manager questions; you are retrieving metadata information from the integration layer.

IMPORTANT NOTE 2: If you have already generated a query (in step 2) to display metadata information of a specific column and this column shows up in several manager queries, you need to execute the query which displays metadata information about this column only once.

Reference no: EM131146340

Questions Cloud

Evaluation of the article conclusion and supporting reasons : Analysis of connection between article's main points and description of interpretation and exegesis in unit lesson and  Evaluation of the article's conclusion and supporting reasons.
Prepare a journal entry to record the purchase of material : Prepare a journal entry to record the purchase of material.- . Prepare a journal entry to record material used in production.
Find the expression for the maximum response : Derive an expression for the time at which the response of a critically damped system will attain its maximum value. Also find the expression for the maximum response.
What stereotypes - biases and assumptions are at play : Explain how the social variables and determinants influencing the development of the social problem vary depending on whether the social problem is local or global. In other words, are the previously identified social variables and determinants in..
Canonical representation of the integration layer : Create a metadata integration layer on top of two databases - global users who access both databases by issuing queries to a global (integration) layer which in turn, accesses both databases at the same time.
What is our journal entryies : So assume we own 10% of another company. That company has net income of 80,000 ans makes dividend distributions of 50,000. What is our journal entry(ies)?
Determine the time taken to reach the maximum displacement : If the stiffness of the spring is k = 80 N/mm and the damping constant is c = 20 N-s/mm determine (a) the maximum displacement of the car after engaging the springs and damper and (b) the time taken to reach the maximum displacement.
What is the projected dividend for the coming year : What is the projected dividend for the coming year? (Do not round intermediate calculations and round your final answer to 2 decimal places. (e.g., 32.16))
Will there be people who are against it : Write about three paragraphs about whether the policy is financially feasible - can it be done with the money they will budget for the policy? Will it be too expensive? is it politically feasible? Will people support it?

Reviews

Write a Review

Data Structure & Algorithms Questions & Answers

  Implement an open hash table

In this programming assignment you will implement an open hash table and compare the performance of four hash functions using various prime table sizes.

  Use a search tree to find the solution

Explain how will use a search tree to find the solution.

  How to access virtualised applications through unicore

How to access virtualised applications through UNICORE

  Recursive tree algorithms

Write a recursive function to determine if a binary tree is a binary search tree.

  Determine the mean salary as well as the number of salaries

Determine the mean salary as well as the number of salaries.

  Currency conversion development

Currency Conversion Development

  Cloud computing assignment

WSDL service that receives a request for a stock market quote and returns the quote

  Design a gui and implement tic tac toe game in java

Design a GUI and implement Tic Tac Toe game in java

  Recursive implementation of euclids algorithm

Write a recursive implementation of Euclid's algorithm for finding the greatest common divisor (GCD) of two integers

  Data structures for a single algorithm

Data structures for a single algorithm

  Write the selection sort algorithm

Write the selection sort algorithm

  Design of sample and hold amplifiers for 100 msps by using n

The report is divided into four main parts. The introduction about sample, hold amplifier and design, bootstrap switch design followed by simulation results.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd