Object Oriented Database Assignment Script 2

-- 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;
/
-- 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
         )
)
/
--Create ALL_ALBUMS view for Q7 to Q9
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;
/

-- 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)
AND
albumtitle NOT IN (
SELECT DISTINCT albumtitle
--,c.artistname
FROM albums a
--, table(a.ALBUMARTISTS) c
WHERE VALUE(a) IS OF (disk_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)
ORDER BY albumtitle
/
-- Q5 answer is implemented in the member function section from line 79---


-- 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.reviewText, c.reviewScore FROM albums a, TABLE(a.ALBUMREVIEWS) c
WHERE a.containsText(c.ReviewText, 'great') > 0
/