3 attachmentsSlide 1 of 3

  • attachment_1attachment_1
  • attachment_2attachment_2
  • attachment_3attachment_3

UNFORMATTED ATTACHMENT PREVIEW

UMBC IS 676: Information Integration Homework 2: Integration/metadata layer on top of two databases A major step in information integration is to provide access and retrieve information from different repositories, and in our testbed we are using databases. An Inventory database has already been created for a previous homework. From now on we will use that database but we will refer to it in this assignment with a different name – let us call it local DB1 and it belongs to a fictitious company you own (MyComp). MyComp has been doing really well for the past few years and it has merged with another company (BuddyComp) which already has an inventory database – let us call it local DB2. The local DB2 database is actually a set of tables and records that is provided to you as a script containing SQL commands. You need to run this script in your Oracle account to create and populate the tables that comprise the local DB2 database. Actually, you do not have two separate databases in Oracle; instead, you have several tables in your single Oracle account: • • Tables that make up your Inventory database (from HW1) which we now call local DB1 Tables that are generated by the provided script and make up local DB2 The purpose of this homework is to create an integration/metadata/ontology layer on top of these two inventory databases, local DB1 and local DB2. Think of the integration layer as the metadata layer which contains information describing the two databases. This homework is about creating this metadata layer. Creation of a metadata layer. You need to create a metadata layer to be used for integration of information from the participating databases. This metadata layer is a table which contains information about the local database schemas (local DB1 and local DB2, which both reside in your Oracle account. The integration layer table contains the following information: 1. Canonical representation. This is the name that we use to refer to an object (table name, or field name) at the metadata layer. For example, one may use the name “Client” in local DB1, and “Patron” in local DB2. However, these are local names representing customers at each local DB. At the metadata layer you may use the word Customer to refer to either Clients or Patrons. So where do we store the word Customer? Where do we store the information that Clients are Customers and also Patrons are Customers? The answer to these two questions is: In the metadata/integration/ontology layer. In other words, we store this information in the metadata table. How? we designate Customer to be the value in a row that represents customers, under a column called ‘canonical representation’ in the metadata table. Within the same row, we add information about customers in other local databases, e.g. Client under the column Local DB 1. (this is just an example to illustrate the point; there could be different field names in the actual databases). In summary, the canonical representation of an object is the “global” name of that object at the metadata layer. It can be used to describe the two corresponding names of the same 1 object, one in local DB1, and the other in local DB2. Customer (canonical name); Client (local DB1); Patron (local DB2) – all these three names within the same row of the metadata table. 2. Data Types and other semantic differences. This is a description of the data types used for each column name in each local DB. For example, varchar2(20), number, date, etc. You may also store the name of a function that converts data from the canonical representation to local databases. 3. Additional fields. If you want, you may use additional information about the correspondences or translations between canonical to local. Implementing the metadata layer: You may use any structure of table(s) to describe information that is stored as data in the two participating databases (local DB 1 and local DB 2). This is a very important task since it provides the basis for the integration. It is suggested that for each object (table name, column name, etc.) that is present in each local db, there should be three representations (in the same row in the metadata table): 1. a canonical representation (global level) for the object. This is a representation that identifes the object globally (see first column in the example table below). 2. a local representation for local DB1: it refers to the name of the column in local DB1 that represents that same object. Also we create another column in the metadata table to store the data type of that column in local DB1 (see columns 2 and 3 in example table below) 3. a local representation for local DB2: it refers to the name of the column in local DB2 that represents that same object. In addition, we need to store its data type in local DB2 (see columns 4 and 5 in example table below) To illustrate this clearly, let’s taka a look at an example implementation of a metadata table below for a very simple representation of two objects: a customer (represented in both DBs) and a product ID (also in both DBs). Note that all data entered in that table are strings (varchar2): Canonical Representation ‘Customer’ ‘Product_ID’ … Column name in local DB1 ‘Client’ ‘CD_ID’ … Data Type in local DB1 ‘Varchar2(20)’ ‘Varchar2(10)’ … Column name in local DB2 ‘Patron’ ‘CD’ … Data Type in local DB2 ‘Char(50)’ ‘number’ … You may need to add more fields to be able to capture semantic and syntactic differences that are present in the two local databases. Do not create views on top of the existing tables of local DB1 and local DB2. You need to create an actual table with metadata about DB1 tables and fields, and DB2 tables and fields, similar to the example. The metadata (integration layer) table, along with the actual local DB1 and local DB2 tables are stored under a single Oracle account (basically, they are just tables in your Oracle account). How can one create a metadata/integration layer? Look at the schema of local DB1. You already know that pretty well, since you created it. Look at the schema of local DB2. Study it carefully, and make sure you completely understand it. Identify semantically similar fields between the two local DBs and for each field you need to enter its description (name, data type, mapping function) in the same row in the metadata table. The metadata table contains the following columns: 2 • • • • • • • Column 1: Contains the name of a field (canonical representation). Column 2: Contains the corresponding name of the same field in DB1 (local DB1 name) Column 3: Contains the name of the data type of that field in DB1 Column 4: Contains a function stored as a string, that maps the canonical name to the DB1 name (if applicable) Column 5: Contains the corresponding name (local DB2 name) of the same field in DB2 Column 6: Contains the data type of that field in DB2 Column 7: Contains a function to map the canonical name to the DB2 name (if applicable) The information listed in the bullet list above describes a single field and the way it is represented as canonical, localDB1 and localDB2. This information about this field is inserted as a single row in the metadata table. If you need additional fields, (e.g., ways to convert from local to canonical – such as CONCAT (f1, f2), back and forth) feel free to add additional column(s) in your integration table and populate them accordingly. • A field that exists in one local DB but not in the other has a NULL value in the columns of the local DB that is not present. • For aggregate fields type the function as a string that puts them together (CONCAT, SUM, etc.) • Add table names (one row per table name) also in the metadata table (just as you have added fields). The data type for tables should be the string ‘TABLE’ Let me reiterate that this assignment is about metadata – not actual local DB values. The final output of this assignment is to make sure that you have enough information in your metadata (integration) layer that describes the local DBs. DELIVERABLES: Create a file in MS Word or pdf and upload it on Blackboard containing the following: 1. Populating Local DB2. Run the script that creates and populates the local DB2 database. It has been posted on Bb. Download it, study the relational schema and run it on your Oracle account to create local DB2. Preservation of local schemas. You are NOT allowed to modify the schema of the two existing databases (local DB1 and local DB2). However, you may insert more data records in any existing table. a. [20 points] Insert records in local DB1: 2 CDs of your choice and 2 books of your choice. b. [30 points] Insert the same records in the schema of local DB2. Submit the INSERT commands that you have used for a. and b., and also show a screen shot with the new records. 2. Creation of the integration (metadata) layer c. [20 points] E-R diagram of your integration layer (this is a very very simple E-R diagram) d. [30 points] A screen-shot illustrating the content of your metadata table (run a “select * from …” for the metadata table to show its content). You may need to run multiple queries if you have a lot of data to show. Note: This applies to the metadata table, not the local DB2 tables. 3 — — This is the local DB2 database. Login to your Oracle account and run this script — create table amazing_warehouse (amazing_w_id number, amazing_w_name varchar2(400), amazing_w_street varchar2(400), amazing_w_city varchar2(400), amazing_w_state varchar2(400), amazing_w_zip varchar2(400), CONSTRAINT warehouse_pk PRIMARY KEY(amazing_w_id) ); insert into amazing_warehouse values (10, ‘Warehouse 1’, ‘100 Main St.’, ‘New Yorky’, ‘MD’, ‘21000’); insert into amazing_warehouse values (20, ‘Warehouse 2’, ‘200 Main St.’, ‘Los Pappas’, ‘NJ’, ‘07000’); insert into amazing_warehouse values (30, ‘Warehouse 3’, ‘300 Main St.’, ‘Poe Polis’, ‘CA’, ‘90000’); insert into amazing_warehouse values (40, ‘Warehouse 4’, ‘400 Main St.’, ‘Ocean Land’, ‘NY’, ‘10000’); insert into amazing_warehouse values (50, ‘Warehouse 5’, ‘500 Main St.’, ‘Flamingo’, ‘AZ’, ‘85000’); create table amazing_books (amazing_b_id number(10), amazing_b_title varchar2(400), amazing_b_author varchar2(400), amazing_b_publisher varchar2(400), amazing_b_price number(10, 2), amazing_b_num_of_copies number, amazing_b_stored_at number, CONSTRAINT books_pk PRIMARY KEY (amazing_b_id), CONSTRAINT fk_books FOREIGN KEY (amazing_b_stored_at) REFERENCES amazing_warehouse(amazing_w_id) ); insert into amazing_books values (1, ‘Book 1’, ‘Author1, Author3’, ‘Publisher1’, 29.99, 103, 10); insert into amazing_books values (2, ‘Book 2’, ‘Author1’, ‘Publisher1’, 19.99, 105, 10); insert into amazing_books values (3, ‘Book 3’, ‘Author1, Author5’, ‘Publisher2’, 39.99, 271, 20); insert into amazing_books values (4, ‘Book 4’, ‘Author2’, ‘Publisher1’, 29.99, 151, 10); insert into amazing_books values (5, ‘Book 5’, ‘Author2, Author3’, ‘Publisher2’, 39.99, 104, 20); insert into amazing_books values (6, ‘Book 6’, ‘Author2’, ‘Publisher2’, 29.99, 223, 30); insert into amazing_books values (7, ‘Book 7’, ‘Author2, Author1’, ‘Publisher4’, 19.99, 91, 40); insert into amazing_books values (8, ‘Book 8’, ‘Author3’, ‘Publisher3’, 9.99, 28, 10); insert into amazing_books values (9, ‘Book 9’, ‘Author3’, ‘Publisher3’, 29.99, 10, 10); insert into amazing_books values (10, ‘Book 10’, ‘Author4’, ‘Publisher3’, 19.99, 120, 50); insert into amazing_books values (11, ‘Book 11’, ‘Author4, Author1’, ‘Publisher2’, 29.99, 135, 50); insert into amazing_books values (12, ‘Book 12’, ‘Author4’, ‘Publisher2’, 39.99, 113, 40); insert into amazing_books values (13, ‘Book 13’, ‘Author4’, ‘Publisher1’, 49.99, 27, 30); insert into amazing_books values (14, ‘Book 14’, ‘Author5’, ‘Publisher4’, 9.99, 98, 10); insert into amazing_books values (15, ‘Book 15’, ‘Author5, Author2’, ‘Publisher4’, 19.99, 152, 20); insert into amazing_books values (16, ‘Book 16’, ‘Author5’, ‘Publisher3’, 29.99, 118, 30); insert into amazing_books values (17, ‘Book 17’, ‘Author5, Author4’, ‘Publisher3’, 19.99, 244, 40); insert into amazing_books values (18, ‘Book 18’, ‘Author5’, ‘Publisher2’, 9.99, 308, 50); insert into amazing_books values (19, ‘Book 19’, ‘Author6, Author1’, ‘Publisher4’, 19.99, 321, 10); insert into amazing_books values (20, ‘Book 20’, ‘Author6, Author1’, ‘Publisher1’, 29.99, 722, 20); insert into amazing_books values (21, ‘Book 21’, ‘Author6’, ‘Publisher1’, 39.99, 130, 30); create table amazing_cds (amazing_c_id number, amazing_c_title varchar2(400), amazing_c_num_songs number, amazing_c_producer varchar2(400), amazing_c_price number (10, 2), amazing_c_num_of_copies number, amazing_c_stored_at number, CONSTRAINT cds_pk PRIMARY KEY (amazing_c_id), CONSTRAINT fk_cds FOREIGN KEY (amazing_c_stored_at) REFERENCES amazing_warehouse(amazing_w_id) ); insert into amazing_cds values (1, ‘CD1’, 10, ‘Producer1’, 19.99, 202, 10); insert into amazing_cds values (2, ‘CD2’, 11, ‘Producer1’, 19.99, 432, 20); insert into amazing_cds values (3, ‘CD3’, 13, ‘Producer2’, 19.99, 311, 10); insert into amazing_cds values (4, ‘CD4’, 12, ‘Producer2’, 9.99, 151, 30); insert into amazing_cds values (5, ‘CD5’, 16, ‘Producer2’, 29.99, 721, 20); insert into amazing_cds values (6, ‘CD6’, 13, ‘Producer3’, 9.99, 321, 20); insert into amazing_cds values (7, ‘CD7’, 12, ‘Producer3’, 19.99, 520, 30); insert into amazing_cds values (8, ‘CD8’, 10, ‘Producer3’, 29.99, 97, 50); insert into amazing_cds values (9, ‘CD9’, 17, ‘Producer3’, 19.99, 84, 30); insert into amazing_cds values (10, ‘CD10’, 9, ‘Producer4’, 9.99, 340, 40); insert into amazing_cds values (11, ‘CD11’, 10, ‘Producer4’, 9.99, 211, 40); insert into amazing_cds values (12, ‘CD12’, 15, ‘Producer4’, 19.99, 904, 40); insert into amazing_cds values (13, ‘CD13’, 17, ‘Producer4’, 29.99, 409, 50); insert into amazing_cds values (14, ‘CD14’, 13, ‘Producer4’, 19.99, 332, 50); insert into amazing_cds values (15, ‘CD15’, 10, ‘Producer1’, 9.99, 122, 50); –Adrienne DeSalvatore –This script is to create a database schema to store inventory information for warehouses and the books/cds that are stored at them. –The script drops all tables before creating them, filling them with data, then querying the data. –DROP ALL TABLES DROP TABLE INVENTORY; DROP TABLE PRODUCTS; DROP TABLE BOOKS; DROP TABLE ALBUMS; DROP TABLE GENRES; DROP TABLE RECORDCOMPS; DROP TABLE WAREHOUSES; DROP TABLE ARTISTS; DROP TABLE AUTHORS; –CREATE TABLES CREATE TABLE AUTHORS ( authorID number NOT NULL, lastName varchar(30) NOT NULL, firstName varchar(20), CONSTRAINT pk_authors PRIMARY KEY (authorID) ); CREATE TABLE ARTISTS ( artistID number NOT NULL, lastName varchar(30) NOT NULL, firstName varchar(20), CONSTRAINT pk_artists PRIMARY KEY (artistID) ); CREATE TABLE WAREHOUSES ( warehouseID number NOT NULL, address varchar(50) NOT NULL, manager varchar(30) NOT NULL, phone varchar(15) NOT NULL, CONSTRAINT pk_warehouses PRIMARY KEY (warehouseID) ); CREATE TABLE RECORDCOMPS ( recCompID number NOT NULL, companyName varchar(20) NOT NULL, CONSTRAINT pk_recordcomps PRIMARY KEY (recCompID) ); CREATE TABLE GENRES ( genreID number NOT NULL, genreName varchar(25), CONSTRAINT pk_genres PRIMARY KEY (genreID) ); CREATE TABLE ALBUMS ( albumID number NOT NULL, albumName varchar(30) NOT NULL, artistID constraint fk_artists_albums REFERENCES ARTISTS(artistID) NOT NULL, recCompID constraint fk_recordComps_albums REFERENCES RECORDCOMPS(recCompID) NOT NULL, releaseYear number, albumPrice number NOT NULL, CONSTRAINT pk_albums PRIMARY KEY (albumID) ); CREATE TABLE BOOKS ( bookID number NOT NULL, bookName varchar(50) NOT NULL, authorID constraint fk_authorss_bookss REFERENCES AUTHORS(authorID) NOT NULL, genreID constraint fk_genres_books REFERENCES GENRES(genreID) NOT NULL, releaseYear number, bookPrice number NOT NULL, isbn13 number, CONSTRAINT pk_books PRIMARY KEY (bookID) ); CREATE TABLE PRODUCTS ( productID number NOT NULL, bookID constraint fk_books_inventory REFERENCES BOOKS(bookID), albumID constraint fk_albums_inventory REFERENCES ALBUMS(albumID), CONSTRAINT pk_products PRIMARY KEY (productID) ); CREATE TABLE INVENTORY ( invID number NOT NULL, warehouseID constraint fk_warehouses_inventory REFERENCES WAREHOUSES(warehouseID), productID constraint fk_products_inventory REFERENCES PRODUCTS(productID), invCount number NOT NULL, CONSTRAINT pk_inventory PRIMARY KEY (invID) ); –INSERT AUTHOR DATA INSERT INTO AUTHORS (authorID, lastName, firstName) VALUES ( 1, ‘Rowling’, ‘J.K.’ ); INSERT INTO AUTHORS (authorID, lastName, firstName) VALUES ( 2, ‘Roberts’, ‘Nora’ ); INSERT INTO AUTHORS (authorID, lastName, firstName) VALUES ( 3, ‘Adams’, ‘Douglas’ ); INSERT INTO AUTHORS (authorID, lastName, firstName) VALUES ( 4, ‘King’, ‘Stephen’ ); INSERT INTO AUTHORS (authorID, lastName, firstName) VALUES ( 5, ‘Card’, ‘Orson Scott’ ); –INSERT ARTIST DATA INSERT INTO ARTISTS (artistID, lastName, firstName) VALUES ( 1, ‘Carey’, ‘Mariah’ ); INSERT INTO ARTISTS (artistID, lastName) VALUES ( 2, ‘The Decemberists’ ); INSERT INTO ARTISTS (artistID, lastName, firstName) VALUES ( 3, ‘Gaye’, ‘Marvin’ ); INSERT INTO ARTISTS (artistID, lastName, firstName) VALUES ( 4, ‘Manson’, ‘Marilyn’ ); INSERT INTO ARTISTS (artistID, lastName) VALUES ( 5, ‘The Who’ ); –INSERT WAREHOUSE DATA INSERT INTO WAREHOUSES (warehouseID, address, manager,phone) VALUES ( 1, ‘100 Fake Drive Washington, DC’, ‘John Smith’, ‘(555)-123-4567’ ); INSERT INTO WAREHOUSES (warehouseID, address, manager,phone) VALUES ( 2, ‘200 Unreal Lane Kensington, MD’, ‘Jane Doe’, ‘(444)-321-7654′ ); INSERT INTO WAREHOUSES (warehouseID, address, manager,phone) VALUES ( 3, ’50 Other Place Wheaton, MD’, ‘Art Vandelay’, ‘(321)-798-1543’ ); INSERT INTO WAREHOUSES (warehouseID, address, manager,phone) VALUES ( 4, ‘1313 Mockinbird Lane Sterling, VA’, ‘Eddie Munster’, ‘(666)-154-8964’ ); INSERT INTO WAREHOUSES (warehouseID, address, manager,phone) VALUES ( 5, ‘221B Baker Street London, England’, ‘Sherlock Holmes’, ‘(165)-245-1987’ ); –INSERT RECORD COMPANY DATA INSERT INTO RECORDCOMPS (recCompID, companyName) VALUES ( 1, ‘Columbia’ ); INSERT INTO RECORDCOMPS (recCompID, companyName) VALUES ( 2, ‘Interscope’ ); INSERT INTO RECORDCOMPS (recCompID, companyName) VALUES ( 3, ‘Capitol’ ); INSERT INTO RECORDCOMPS (recCompID, companyName) VALUES ( 4, ‘Tamla’ ); INSERT INTO RECORDCOMPS (recCompID, companyName) VALUES ( 5, ‘Brunswick’ ); –INSERT GENRE DATA INSERT INTO GENRES (genreID, genreName) VALUES ( 1, ‘Science Fiction’ ); INSERT INTO GENRES (genreID, genreName) VALUES ( 2, ‘Fantasy’ ); INSERT INTO GENRES (genreID, genreName) VALUES ( 3, ‘Horror’ ); INSERT INTO GENRES (genreID, genreName) VALUES ( 4, ‘Romance’ ); INSERT INTO GENRES (genreID, genreName) VALUES ( 5, ‘Childrens’ ); –INSERT ALBUM DATA INSERT INTO ALBUMS (albumID, albumName, artistID, recCompID, releaseYear, albumPrice) VALUES ( 1, ‘Mariah Carey’, 1, 1, 1990, 9.99 ); INSERT INTO ALBUMS (albumID, albumN…

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.