IS 676 University of Maryland Baltimore County Retrieving Metadata Information using SQL Project

UMBC – Department of Information SystemsIS 676: Information Integration
Homework 3: 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 integration layer. In
such an environment, there are two types of users:
•
•
Local users who access a local database directly, by issuing queries to the local schema. These
queries are called local queries.
Global users who access both local 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 metadata layer to get information about canonical queries
(see above). Below is an example excerpt of a metadata table, which contains metadata information about
data in the two participating databases (local DB1 and local DB2).
Canonical
Representation
Customer
Product_ID
…
Column
name
used in local DB1
Client
CD_ID
…
Data Type in
local DB1
Varchar2(20)
Varchar2(10)
Column
name
used in local DB2
Patron
CD
…
Data Type in
local DB2
Char (50)
number
A manager wants to get some information from the local databases and submit a canonical query which
is written in terms of the canonical representation of the integration (metadata) layer.
DELIVERABLE: 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 HW1) 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. [5 points per query, 25 points total] Submit each question as a canonical query: 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 stored as values of the canonical representation column in your metadata table.
Do this for each one of the 5 manager questions (queries).
a. Example: Observe the sample metadata table above. Assume that a query for localDB1 is:
select CD_ID from CD_TABLE where Client = ‘Joe’;
1
b. Canonical representation: The above is a local query. The corresponding canonical query is:
select Product_ID from Products where Customer = ‘Joe’ and Product_Type = ‘CD’ [NOTE: You
manually type this query in the deliverable based on information in your metadata table. All
the column names and table names that appear in the canonical query are canonical
representation terms. Do not try to execute this query in Oracle, it will not run, it will give you
syntax errors]
2. [15 points per query, 75 points total] For each field name and table name appearing in the canonical
query, type, submit, and execute a query against your integration (metadata) layer and show its
results to discover the corresponding names/types/etc. of that field name in canonical representation,
its corresponding 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 a query similar to the
one below, against your metadata table, and display the output 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 questions below:
Manager questions (queries)
1.
2.
3.
4.
5.
List all details of a specific product given its unique identifier
List each product(s) of a particular author or artist
List each product with at least quantity N
List each product that is out of stock
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.
2