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.