1 attachmentsSlide 1 of 1
UNFORMATTED ATTACHMENT PREVIEW
UMBC – Department of Information Systems IS 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
Do you similar assignment and would want someone to complete it for you? Click on the ORDER NOW option to get instant services at essayloop.com
Do you have a similar assignment and would want someone to complete it for you? Click on the ORDER NOW option to get instant services at essayloop.com. We assure you of a well written and plagiarism free papers delivered within your specified deadline.