IS 676 University of Maryland Baltimore County Information Integration

UMBCIS 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, albumName, artistID, recCompID, releaseYear, albumPrice)
VALUES (
2,
‘Antichrist Superstar’,
4,
2,
1996,
5.99
);
INSERT INTO ALBUMS (albumID, albumName, artistID, recCompID, releaseYear, albumPrice)
VALUES (
3,
‘The Crane Wife’,
2,
3,
2006,
14.99
);
INSERT INTO ALBUMS (albumID, albumName, artistID, recCompID, releaseYear, albumPrice)
VALUES (
4,
‘Whats Going On’,
3,
4,
1971,
2.99
);
INSERT INTO ALBUMS (albumID, albumName, artistID, recCompID, releaseYear, albumPrice)
VALUES (
5,
‘My Generation’,
5,
5,
1965,
4.99
);
–INSERT BOOK DATA
INSERT INTO BOOKS (bookID, bookName, authorID, genreID, releaseYear, bookPrice, isbn13)
VALUES (
1,
‘The Hitchhikers Guide to the Galaxy’,
3,
1,
1979,
‘9.99’,
9780345391803
);
INSERT INTO BOOKS (bookID, bookName, authorID, genreID, releaseYear, bookPrice, isbn13)
VALUES (
2,
‘The Restaurant at the End of the Universe’,
3,
1,
1980,
‘9.99’,
9780345391810
);
INSERT INTO BOOKS (bookID, bookName, authorID, genreID, releaseYear, bookPrice, isbn13)
VALUES (
3,
‘Harry Potter and the Sorcerers Stone’,
1,
2,
1997,
‘19.99’,
9780545790352
);
INSERT INTO BOOKS (bookID, bookName, authorID, genreID, releaseYear, bookPrice, isbn13)
VALUES (
4,
‘Harry Potter and the Chamber of Secrets’,
1,
2,
1998,
‘19.99’,
9788498387650
);
INSERT INTO BOOKS (bookID, bookName, authorID, genreID, releaseYear, bookPrice, isbn13)
VALUES (
5,
‘The Villa’,
2,
4,
2021,
‘4.99’,
9780593333334
);
INSERT INTO BOOKS (bookID, bookName, authorID, genreID, releaseYear, bookPrice, isbn13)
VALUES (
6,
‘Doctor Sleep’,
4,
3,
2019,
‘19.99’,
9781982131807
);
INSERT INTO BOOKS (bookID, bookName, authorID, genreID, releaseYear, bookPrice, isbn13)
VALUES (
7,
‘Enders Shadow’,
5,
1,
2000,
‘4.99’,
9780812575712
);
INSERT INTO BOOKS (bookID, bookName, authorID, genreID, releaseYear, bookPrice, isbn13)
VALUES (
8,
‘Seventh Son’,
5,
2,
1987,
‘4.99’,
9780312930196
);
–INSERT PRODUCT DATA
INSERT INTO PRODUCTS (productID, bookID)
VALUES (
1,
1
);
INSERT INTO PRODUCTS (productID, bookID)
VALUES (
2,
2
);
INSERT INTO PRODUCTS (productID, bookID)
VALUES (
3,
3
);
INSERT INTO PRODUCTS (productID, bookID)
VALUES (
4,
4
);
INSERT INTO PRODUCTS (productID, bookID)
VALUES (
5,
5
);
INSERT INTO PRODUCTS (productID, bookID)
VALUES (
6,
6
);
INSERT INTO PRODUCTS (productID, bookID)
VALUES (
7,
7
);
INSERT INTO PRODUCTS (productID, bookID)
VALUES (
8,
8
);
INSERT INTO PRODUCTS (productID, albumID)
VALUES (
9,
1
);
INSERT INTO PRODUCTS (productID, albumID)
VALUES (
10,
2
);
INSERT INTO PRODUCTS (productID, albumID)
VALUES (
11,
3
);
INSERT INTO PRODUCTS (productID, albumID)
VALUES (
12,
4
);
INSERT INTO PRODUCTS (productID, albumID)
VALUES (
13,
5
);
–INSERT INVENTORY DATA BOOKS
INSERT INTO INVENTORY (invID, warehouseID, productID, invCount)
VALUES(
1,
1,
1,
2
);
INSERT INTO INVENTORY (invID, warehouseID, productID, invCount)
VALUES(
2,
2,
1,
5
);
INSERT INTO INVENTORY (invID, warehouseID, productID, invCount)
VALUES(
3,
3,
1,
1
);
INSERT INTO INVENTORY (invID, warehouseID, productID, invCount)
VALUES(
4,
4,
1,
0
);
INSERT INTO INVENTORY (invID, warehouseID, productID, invCount)
VALUES(
5,
5,
1,
2
);
INSERT INTO INVENTORY (invID, warehouseID, productID, invCount)
VALUES(
6,
1,
2,
0
);
INSERT INTO INVENTORY (invID, warehouseID, productID, invCount)
VALUES(
7,
2,
2,
0
);
INSERT INTO INVENTORY (invID, warehouseID, productID, invCount)
VALUES(
8,
3,
2,
0
);
INSERT INTO INVENTORY (invID, warehouseID, productID, invCount)
VALUES(
9,
4,
2,
0
);
INSERT INTO INVENTORY (invID, warehouseID, productID, invCount)
VALUES(
10,
5,
2,
0
);
–QUERIES
–Query 1 – List all products in inventory (just a few columns for each product)
SELECT BOOKS.bookName as “Title”, CONCAT(CONCAT(AUTHORS.firstName, ‘ ‘),AUTHORS.lastName) as “Author”, BOOKS.bookPrice as “Price”, GENRES.genreName as “Genre”, ALBUMS.albumName as “Album”, CONCAT(CONCAT(ARTISTS.firstName, ‘ ‘),ARTISTS.lastName) as “Artist”, ALBUMS.albumPrice as “Price”, RECORDCOMPS.COMPANYNAME as “Studio” FROM PRODUCTS
LEFT OUTER JOIN BOOKS on PRODUCTS.bookID = BOOKS.bookID
LEFT OUTER JOIN AUTHORS on BOOKS.authorID = AUTHORS.authorID
LEFT OUTER JOIN GENRES on BOOKS.genreID = GENRES.genreID
LEFT OUTER JOIN ALBUMS on PRODUCTS.albumID = ALBUMS.albumID
LEFT OUTER JOIN ARTISTS on ALBUMS.artistID = ARTISTS.artistID
LEFT OUTER JOIN RECORDCOMPS on ALBUMS.recCompID = RECORDCOMPS.recCompID
;
–Query 2 – List all details of a product given its unique identifier
SELECT BOOKS.bookName as “Title”, CONCAT(CONCAT(AUTHORS.firstName, ‘ ‘),AUTHORS.lastName) as “Author”, GENRES.genreName “Genre”, BOOKS.releaseYear as “Release”, BOOKS.bookPrice as “Price”, BOOKS.isbn13 as “ISBN-13” from PRODUCTS
RIGHT JOIN BOOKS on PRODUCTS.bookID = BOOKS.bookID
LEFT JOIN AUTHORS on BOOKS.authorID = AUTHORS.authorID
LEFT JOIN GENRES on BOOKS.genreID = GENRES.genreID
WHERE PRODUCTS.productID = 3;
–QUERY 3 – List each product(s) of a particular author or artist
SELECT BOOKS.bookName as “Title”, CONCAT(CONCAT(AUTHORS.firstName, ‘ ‘),AUTHORS.lastName) as “Author”, GENRES.genreName as “Genre”, BOOKS.releaseYear as “Release”, BOOKS.bookPrice as “Price”, BOOKS.isbn13 as “ISBN-13” from PRODUCTS
RIGHT JOIN BOOKS on PRODUCTS.bookID = BOOKS.bookID
LEFT JOIN AUTHORS on BOOKS.authorID = AUTHORS.authorID
LEFT JOIN GENRES on BOOKS.genreID = GENRES.genreID
WHERE AUTHORS.lastName = ‘Card’;
–Query 4 – List each product with at least quantity N (where N is a number)
SELECT WAREHOUSES.address as “Warehouse”, WAREHOUSES.phone as “Contact #”, BOOKS.bookname as “Title”, ALBUMS.albumName as “Album”, INVENTORY.invCount as “Count” from INVENTORY
LEFT JOIN PRODUCTS on INVENTORY.productID = PRODUCTS.productID
LEFT JOIN WAREHOUSES on INVENTORY.warehouseID = WAREHOUSES.warehouseID
LEFT JOIN BOOKS on PRODUCTS.bookID = BOOKS.bookID
LEFT JOIN ALBUMS on PRODUCTS.albumID = ALBUMS.albumID
WHERE INVENTORY.invCount >= 2;
–Query 5 – List each product that is out of stock
SELECT WAREHOUSES.address as “Warehouse”, WAREHOUSES.phone as “Contact #”, BOOKS.bookname as “Title”, ALBUMS.albumName as “Album”, INVENTORY.invCount as “Count” from INVENTORY
LEFT JOIN PRODUCTS on INVENTORY.productID = PRODUCTS.productID
LEFT JOIN WAREHOUSES on INVENTORY.warehouseID = WAREHOUSES.warehouseID
LEFT JOIN BOOKS on PRODUCTS.bookID = BOOKS.bookID
LEFT JOIN ALBUMS on PRODUCTS.albumID = ALBUMS.albumID
WHERE INVENTORY.invCount 0 and
INVENTORY.productID = 1;