Object Oriented Database Assignment DDL

-- create OMDB --
-- drop tables --
drop view all_albums
/
drop table albums
/
drop type disk_type
/
drop type mp3_type
/
drop type album_type
/
drop type artist_array_type
/
drop type artist_type
/
drop type review_table_type
/
drop type review_type
/
-- create types --
create or replace type artist_type as object
(artistName 	varchar(50),
 artistRole 	varchar(25))
/
create type artist_array_type
as varray(5) of artist_type
/
create or replace type review_type as object
(reviewerName 	varchar(25),
 reviewDate   	date,
 reviewText   	varchar(250),
 reviewScore  	number)
/
create or replace type review_table_type as table of review_type
/
create or replace type album_type as object
(albumTitle 		varchar(50),
 albumPlaytime 		number(3), -- minutes
 albumReleaseDate 	date,
 albumGenre 		varchar(15),
 albumPrice 		number(9,2),
 albumTracks		number(2),
 albumArtists		artist_array_type,
 albumReviews 		review_table_type,
member function discountPrice return number,
member function containsText (pString1 varchar2, pString2 varchar2) return integer)
not instantiable not final
/
create or replace type disk_type under album_type
( mediaType 		varchar(10),
 diskNum			number(2), -- number of disks
 diskUsedPrice 		number(9,2),
 diskDeliveryCost 	number(9,2),
overriding member function discountPrice return number)
/
create or replace type mp3_type under album_type
(downloadSize 	number, -- size in MB
 overriding member function discountPrice return number)
/
/
-- Implement function
create or replace type body album_type
as
    member function discountPrice return number is
    v_discount number(9,2);
        BEGIN
             null;
        END;
    member function containsText (pString1 varchar2, pString2 varchar2) return integer is
        BEGIN
            if INSTR(lower(pString1),lower(pString2)) > 0  then return 1;
            else return 0;
            end if;
        END;
END;
/
-- Overriding Function
create or replace type body disk_type as
overriding member function discountPrice
    return number is v_discount number(9,2);
    begin
        if mediaType = 'Vinyl' and (albumReleaseDate < (Sysdate - interval '1' year)) then return (0.8*albumPrice);
        else if mediaType = 'Audio CD' and (albumReleaseDate < (Sysdate - interval '1' year)) then return (0.85*albumPrice);
        else return albumPrice;
        end if;
        end if;
    end;
end;
/
create or replace type body mp3_type as
overriding member function discountPrice
    return number is v_discount number(9,2);
    begin
        if albumReleaseDate < (Sysdate - interval '2' year) then return (0.9*albumPrice);
        else return albumPrice;
        end if;
    end;
end;
/
--- MP3 TYPE --
create or replace type body mp3_type as
overriding member function discountPrice
    return number is v_discount number(9,2);
    begin
        if albumReleaseDate < (Sysdate - interval '2' year) then return (0.9*albumPrice);
        else return albumPrice;
        end if;
    end;
end;
/
-- CONTAINS
--create or replace type body disk_type as
--overriding member function containsText
--    return integer  is StringDoesContain integer;
--    begin
--        if Contains(lower(pString1),lower(pString2)) then return 1;
--        else return 0;
--        end if;
--    end;
--end;
/
-- create tables --
create table albums of album_type
object id system generated
nested table albumReviews store as store_reviews
/
-----------
-- Vinyl --
-----------
INSERT INTO albums
values(
disk_type('The Essential Bob Dylan', 99, '8-Jul-2016', 'Pop', 37.00, 32,
            artist_array_type( artist_type('Bob Dylan', 'Composer'),
                               artist_type('Bob Dylan', 'Vocals')
                             ),
            review_table_type( review_type('Shawn', '2-Aug-2019', 'Great compilation of some of his most known songs!', 5),
                               review_type('Reuben', '18-Feb-2013', 'Captures Bob Dylan transformation from a folk song composer', 5)
                              ),
            'Vinyl', 2, null, 11.0
         )
)
/
INSERT INTO albums
values(
disk_type('Sketches of Spain', 45, '8-Mar-2011', 'Jazz', 14.99, 6,
            artist_array_type( artist_type('Miles Davis', 'Composer'),
                               artist_type('Miles Davis', 'Musician')
                             ),
            review_table_type( review_type('Frederick', '16-Sep-2016', 'Recommend listening while viewing sunset', 5),
                               review_type('Juliet', '12-Mar-2018', 'Early days of the Great Miles--no lover of jazz should be without this album.', 5)
                              ),
            'Vinyl', 1, 16.29, 7.00
         )
)
/
INSERT INTO albums
values(
disk_type('Bob Dylan''s Greatest Hits',45,'31-Jan-2017','Pop Rock',29.87,10,
            artist_array_type(artist_type('Bob Dylan','Composer'),
                              artist_type('Bob Dylan','Vocals')
                                            ),
            review_table_type(
                                    review_type('Kandy','16-Mar-2015','Early Dylan in all his glory.',5),
                                    review_type('Steward','18-Feb-2013','Captures Bob Dylan transformation from a folk song Composer to a rock legend',4)
                                    ),  'Vinyl',1,null,11.00
                )
)

/
INSERT INTO albums
values(
disk_type('Harvest (2009 Remaster)', 44, '21-Jun-2009', 'Rock Country', 28.50, 10,
            artist_array_type( artist_type('Neil Young', 'Composer'),
                               artist_type('Neil Young', 'Vocals')
                             ),
            review_table_type( review_type('John', '18-Feb-2019', 'I absolutely LOVE this CD!', 5),
                               review_type('Stewart', '18-Feb-2013', 'Sound good in vinyl!', 5)
                              ),
            'Vinyl', 1, 14.99, 11.00
         )
)
/
INSERT INTO albums
values(
disk_type('Kind of Blue (Legacy Edition)', 155, '20-Jan-2009', 'Jazz', 19.99, 21,
            artist_array_type( artist_type('Miles Davis', 'Composer'),
                               artist_type('Miles Davis', 'Musician')
                             ),
            review_table_type( review_type('Laurence', '10-Sep-2014', 'Very very special recording.', 5)
                              ),
            'Vinyl', 3, 16.99, 10.00
         )
)
/
--Audio CDs--
INSERT INTO albums
values(
disk_type('Harvest (2009 Remaster)', 44, '21-Jun-2009', 'Rock Country', 10.50, 10,
            artist_array_type( artist_type('Neil Young', 'Composer'),
                               artist_type('Neil Young', 'Vocals')
                             ),
            review_table_type( review_type('John', '18-Feb-2019', 'I absolutely LOVE this CD!', 5),
                               review_type('Anthony', '16-Aug-2019', 'Neil Young''s signature album.', 4)
                              ),

            'Audio CD', 1, 4.99, 11.00
         )
)
/
INSERT INTO albums
values(
disk_type('The Essential Bob Dylan', 99, '8-Jul-2016', 'Pop', 26.17, 32,
            artist_array_type( artist_type('Bob Dylan', 'Composer'),
                               artist_type('Bob Dylan', 'Vocals')
                             ),
            review_table_type( review_type('Christopher', '24-Jun-2016', 'This is a terrific album.', 5),
                               review_type('Cauley', '2-Aug-2015', 'There can only be one Bob Dylan. God blessed him with the gift of verse.', 5)
                              ),
            'Audio CD', 2, null, 7.00
         )
)
/
INSERT INTO albums
values(
disk_type('Bob Dylan''s Greatest Hits', 50, '1-Jun-1999', 'Pop Rock', 20.81, 10,
            artist_array_type( artist_type('Bob Dylan', 'Composer'),
                               artist_type('Bob Dylan', 'Vocals')
                             ),
            review_table_type( review_type('Kandy', '16-Mar-2015', 'Early Dylan in all his glory.', 5),
                               review_type('Stewart', '18-Feb-2013', 'Captures Bob Dylan transformation from a folk song composer to a rock legend.', 4)
                              ),
            'Audio CD', 1, null, 7.00
         )
)
/
INSERT INTO albums
values(
disk_type('Kind Of Blue (Legacy Edition)', 155, '20-Jan-2009', 'Jazz',19.99, 21,
            artist_array_type( artist_type('Miles Davis', 'Composer'),
                               artist_type('Miles Davis', 'Musician')
                             ),
            review_table_type( review_type('Amy', '17-Apr-2018', 'Poor quality sound compared to the vinyl record.', 2)
                              ),
            'Audio CD', 3, 16.99, 10.00
         )
)

/
INSERT INTO albums
values(
disk_type('Sketches of Spain', 45, '20-Jan-2009', 'Jazz', 3.11, 6,
            artist_array_type( artist_type('Miles Davis', 'Composer'),
                               artist_type('Miles Davis', 'Musician')
                             ),
            review_table_type( review_type('Sara', '3-Oct-2016', 'Another must have! One of Miles finest works', 5),
                               review_type('Douglas', '14-Jun-2014', 'You might like it, but I admit it seems like a difficult listen.', 5)
                              ),
            'Audio CD', 1, 6.41, 7.00
         )
)
/
INSERT INTO albums
values(
disk_type('Gustav Mahler Symphony No.9 ', 45, '12-Oct-2017', 'Classical', 23.10, 5,
            artist_array_type( artist_type('David Zinman', 'Conductor'),
                               artist_type('Gustav Miller', 'Composer'),
                               artist_type('Tonhalle Orchaestra', 'Orchestra')
                             ),
            review_table_type( review_type('Lindon', '3-Dec-2010', 'This is an uneventful but fine recording.', 3),
                               review_type('Prescott', '24-Aug-2013', 'This is truly a spellbinding record.', 5)
                              ),
            'Audio CD', 1, 15.20, 7.00
         )
)
/
---MP3 Queries---
INSERT INTO albums
values(
mp3_type('Bob Dylan''s Greatest Hits', 55, '1-Jan-2019', 'Pop Rock', 5.98, 10,
            artist_array_type(
                               artist_type('Bob Dylan', 'Composer'),
                               artist_type('Bob Dylan', 'Vocals')
                             ),
            review_table_type( review_type('Mandy', '16-Mar-2019', 'Fantastic music!.', 5)
                              ),
          60
         )
)
/
INSERT INTO albums
values(
mp3_type('Best of Neil Young', 153, '21-Feb-2019', 'Pop Rock', 17.50, 35,
            artist_array_type( artist_type('Neil Young', 'Composer'),
                               artist_type('Neil Young', 'Vocals')
                             ),
            review_table_type( review_type('John', '16-Apr-2019', 'Great artist and great music.', 5)
                              ),
          165
         )
)
/
INSERT INTO albums
values(
mp3_type('Harvest (2009 Remaster)', 44, '21-Jun-2009', 'Rock Country', 9.49, 10,
            artist_array_type( artist_type('Neil Young', 'Composer'),
                               artist_type('Neil Young', 'Vocals')
                             ),
            review_table_type( review_type('John', '16-Apr-2019', 'Great artist and great music.', 5)
                              ),
          52
         )
)
/
INSERT INTO albums
values(
mp3_type('Sketches of Spain', 45, '16-Aug-2013', 'Jazz', 24.99, 6,
            artist_array_type( artist_type('Miles Davis', 'Composer'),
                               artist_type('Miles Davis', 'Musician')
                             ),
            review_table_type( review_type('Douglas', '14-Jun-2014', 'You might like it but I admit it seems like a difficult listen.', 5)
                              ),
          51
         )
)
/
INSERT INTO albums
values(
mp3_type('B.B. King Greatest Hits', 114, '16-Jul-2013', 'Rock Blues', 11.49, 24,
            artist_array_type( artist_type('B.B. King', 'Vocals'),
                               artist_type('B.B. King', 'Guitar')
                             ),
            review_table_type( review_type('David', '18-May-2015', 'You might like it but I admit it seems like a difficult listen.', 5)

                              ),
          125
         )
)
/
INSERT INTO albums
values(
mp3_type('The Essential Bob Dylan', 99, '8-Jul-2016', 'Pop', 16.00, 32,
            artist_array_type( artist_type('Bob Dylan', 'Composer'),
                               artist_type('Bob Dylan', 'Vocals')
                             ),
            review_table_type( review_type('Christopher', '24-Jun-2016', 'This is a terrific album.', 5),
                                    review_type('Cauley', '2-Apr-2015', 'There can only be one Bob Dylan. God blessed him with the gift of verse', 5)
                              ),
          112
         )
)
/
INSERT INTO albums
values(
mp3_type('Other Peoples Lives', 42, '15-Feb-2019', 'Rock Dance', 9.49, 10,
            artist_array_type( artist_type('Stats', 'Composer'),
                               artist_type('Stats', 'Vocals')
                             ),
            review_table_type( review_type('George', '17-Sep-2019', 'Good dancing music.', 3)
                              ),
          45
         )
)
/
-- Query 1 --
select DISTINCT ALBUMTITLE, ALBUMRELEASEDATE, ALBUMPRICE from albums a, table(a.ALBUMARTISTS) c
where ALBUMRELEASEDATE  > '1-Jan-2015' and c.artistName = 'Neil Young'
/
-- Query 2 =-
select DISTINCT albumtitle, c.artistname from albums a, table(a.ALBUMARTISTS) c
where value(a) IS OF (MP3_TYPE)
order by ALBUMTITLE
/
-- Query 3 --
with abc as     (
                select albumTitle, avg(c.reviewScore) as averageScore from albums a, (table(a.ALBUMREVIEWS)) c
                where value(a) IS OF (MP3_TYPE)
                having count(c.reviewScore) > 1
                group by albumTitle
                )
select * from abc
where averageScore = (select min(averageScore) from abc)
/
--- Query 4 ---
select albumtitle from albums a
where value(a) IS OF (DISK_TYPE)
INTERSECT
select albumtitle from albums a
where value(a) IS OF (MP3_TYPE)
/
-- Q5 answer is on line ---
-- View for Q6 and Q7
create or replace view all_albums (albumTitle, albumType, albumPrice, discount, albumUsedPrice) as
    select a.albumTitle, coalesce(treat(value(a) as Disk_Type).MediaType,'MP3'),
    albumPrice, (albumPrice-a.discountPrice()),
    coalesce(treat(value(a) as Disk_Type).diskUsedPrice , 0)
    from albums a;
/
-- Q6 Select the largest discount
select * from all_albums
where discount = (select max(discount) from all_albums)
/
--- Q7 Select the largest usedPrice
select * from all_albums
where albumUsedPrice = (select max(albumUsedPrice) from all_albums)
/
-- Q8 Select AlbumTitle, ReviewScore that has word great
select DISTINCT albumTitle, c.reviewScore from albums a, table(a.ALBUMREVIEWS) c
where a.containsText(c.ReviewText, 'great') > 0
/