SHARE
TWEET

Object Oriented Database Assignment Script 2

Urthor Oct 6th, 2019 116 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- create OMDB --
  2. -- drop tables --
  3. DROP VIEW all_albums
  4. /
  5. DROP TABLE albums
  6. /
  7. DROP TYPE disk_type
  8. /
  9. DROP TYPE mp3_type
  10. /
  11. DROP TYPE album_type
  12. /
  13. DROP TYPE artist_array_type
  14. /
  15. DROP TYPE artist_type
  16. /
  17. DROP TYPE review_table_type
  18. /
  19. DROP TYPE review_type
  20. /
  21. -- create types --
  22. CREATE OR REPLACE TYPE artist_type AS object
  23. (artistName     VARCHAR(50),
  24.  artistRole     VARCHAR(25))
  25. /
  26. CREATE TYPE artist_array_type  
  27. AS varray(5) OF artist_type
  28. /
  29. CREATE OR REPLACE TYPE review_type AS object
  30. (reviewerName   VARCHAR(25),
  31.  reviewDate     DATE,
  32.  reviewText     VARCHAR(250),
  33.  reviewScore    NUMBER)
  34. /
  35. CREATE OR REPLACE TYPE review_table_type AS TABLE OF review_type
  36. /
  37. CREATE OR REPLACE TYPE album_type AS object
  38. (albumTitle         VARCHAR(50),
  39.  albumPlaytime      NUMBER(3), -- minutes
  40.  albumReleaseDate   DATE,
  41.  albumGenre         VARCHAR(15),
  42.  albumPrice         NUMBER(9,2),
  43.  albumTracks        NUMBER(2),
  44.  albumArtists       artist_array_type,
  45.  albumReviews       review_table_type,
  46. member FUNCTION discountPrice RETURN NUMBER,
  47. member FUNCTION containsText (pString1 VARCHAR2, pString2 VARCHAR2) RETURN INTEGER)
  48. NOT instantiable NOT final
  49. /
  50. CREATE OR REPLACE TYPE disk_type under album_type
  51. ( mediaType         VARCHAR(10),
  52.  diskNum            NUMBER(2), -- number of disks
  53.  diskUsedPrice      NUMBER(9,2),
  54.  diskDeliveryCost   NUMBER(9,2),
  55. overriding member FUNCTION discountPrice RETURN NUMBER)
  56. /
  57. CREATE OR REPLACE TYPE mp3_type under album_type
  58. (downloadSize   NUMBER, -- size in MB
  59.  overriding member FUNCTION discountPrice RETURN NUMBER)
  60. /
  61. /
  62. -- Implement function
  63. CREATE OR REPLACE TYPE BODY album_type
  64. AS
  65.     member FUNCTION discountPrice RETURN NUMBER IS
  66.     v_discount NUMBER(9,2);
  67.         BEGIN
  68.              NULL;
  69.         END;
  70.     member FUNCTION containsText (pString1 VARCHAR2, pString2 VARCHAR2) RETURN INTEGER IS
  71.         BEGIN
  72.             IF INSTR(LOWER(pString1),LOWER(pString2)) > 0  THEN RETURN 1;
  73.             ELSE RETURN 0;
  74.             END IF;
  75.         END;
  76. END;
  77. /
  78. -- Overriding Function
  79. CREATE OR REPLACE TYPE BODY disk_type AS
  80. overriding member FUNCTION discountPrice
  81.     RETURN NUMBER IS v_discount NUMBER(9,2);
  82.     BEGIN
  83.         IF mediaType = 'Vinyl' AND (albumReleaseDate < (SYSDATE - INTERVAL '1' YEAR)) THEN RETURN (0.8*albumPrice);      
  84.         ELSE IF mediaType = 'Audio CD' AND (albumReleaseDate < (SYSDATE - INTERVAL '1' YEAR)) THEN RETURN (0.85*albumPrice);
  85.         ELSE RETURN albumPrice;
  86.         END IF;
  87.         END IF;
  88.     END;
  89. END;
  90. /
  91. CREATE OR REPLACE TYPE BODY mp3_type AS
  92. overriding member FUNCTION discountPrice
  93.     RETURN NUMBER IS v_discount NUMBER(9,2);
  94.     BEGIN
  95.         IF albumReleaseDate < (SYSDATE - INTERVAL '2' YEAR) THEN RETURN (0.9*albumPrice);
  96.         ELSE RETURN albumPrice;
  97.         END IF;
  98.     END;
  99. END;
  100. /
  101. --- MP3 TYPE --
  102. CREATE OR REPLACE TYPE BODY mp3_type AS
  103. overriding member FUNCTION discountPrice
  104.     RETURN NUMBER IS v_discount NUMBER(9,2);
  105.     BEGIN
  106.         IF albumReleaseDate < (SYSDATE - INTERVAL '2' YEAR) THEN RETURN (0.9*albumPrice);
  107.         ELSE RETURN albumPrice;
  108.         END IF;
  109.     END;
  110. END;
  111. /
  112. -- create tables --
  113. CREATE TABLE albums OF album_type
  114. object id system generated
  115. nested TABLE albumReviews store AS store_reviews
  116. /
  117.  
  118. -----------
  119. -- Vinyl --
  120. -----------
  121. INSERT INTO albums
  122. VALUES(
  123. disk_type('The Essential Bob Dylan', 99, '8-Jul-2016', 'Pop', 37.00, 32,
  124.             artist_array_type( artist_type('Bob Dylan', 'Composer'),
  125.                                artist_type('Bob Dylan', 'Vocals')
  126.                              ),
  127.             review_table_type( review_type('Shawn', '2-Aug-2019', 'Great compilation of some of his most known songs!', 5),
  128.                                review_type('Reuben', '18-Feb-2013', 'Captures Bob Dylan transformation from a folk song composer', 5)
  129.                               ),
  130.             'Vinyl', 2, NULL, 11.0
  131.          )
  132. )
  133. /
  134. INSERT INTO albums
  135. VALUES(
  136. disk_type('Sketches of Spain', 45, '8-Mar-2011', 'Jazz', 14.99, 6,
  137.             artist_array_type( artist_type('Miles Davis', 'Composer'),
  138.                                artist_type('Miles Davis', 'Musician')
  139.                              ),
  140.             review_table_type( review_type('Frederick', '16-Sep-2016', 'Recommend listening while viewing sunset', 5),
  141.                                review_type('Juliet', '12-Mar-2018', 'Early days of the Great Miles--no lover of jazz should be without this album.', 5)
  142.                               ),
  143.             'Vinyl', 1, 16.29, 7.00
  144.          )
  145. )
  146. /
  147. INSERT INTO albums
  148. VALUES(
  149. disk_type('Bob Dylan''s Greatest Hits',45,'31-Jan-2017','Pop Rock',29.87,10,
  150.             artist_array_type(artist_type('Bob Dylan','Composer'),
  151.                               artist_type('Bob Dylan','Vocals')
  152.                                             ),
  153.             review_table_type(
  154.                                     review_type('Kandy','16-Mar-2015','Early Dylan in all his glory.',5),
  155.                                     review_type('Steward','18-Feb-2013','Captures Bob Dylan transformation from a folk song Composer to a rock legend',4)
  156.                                     ),  'Vinyl',1,NULL,11.00
  157.                 )
  158. )
  159.  
  160. /
  161. INSERT INTO albums
  162. VALUES(
  163. disk_type('Harvest (2009 Remaster)', 44, '21-Jun-2009', 'Rock Country', 28.50, 10,
  164.             artist_array_type( artist_type('Neil Young', 'Composer'),
  165.                                artist_type('Neil Young', 'Vocals')
  166.                              ),
  167.             review_table_type( review_type('John', '18-Feb-2019', 'I absolutely LOVE this CD!', 5),
  168.                                review_type('Stewart', '18-Feb-2013', 'Sound good in vinyl!', 5)
  169.                               ),
  170.             'Vinyl', 1, 14.99, 11.00
  171.          )
  172. )
  173. /
  174. INSERT INTO albums
  175. VALUES(
  176. disk_type('Kind of Blue (Legacy Edition)', 155, '20-Jan-2009', 'Jazz', 19.99, 21,
  177.             artist_array_type( artist_type('Miles Davis', 'Composer'),
  178.                                artist_type('Miles Davis', 'Musician')
  179.                              ),
  180.             review_table_type( review_type('Laurence', '10-Sep-2014', 'Very very special recording.', 5)
  181.                               ),
  182.             'Vinyl', 3, 16.99, 10.00
  183.          )
  184. )
  185. /
  186. --Audio CDs--
  187. INSERT INTO albums
  188. VALUES(
  189. disk_type('Harvest (2009 Remaster)', 44, '21-Jun-2009', 'Rock Country', 10.50, 10,
  190.             artist_array_type( artist_type('Neil Young', 'Composer'),
  191.                                artist_type('Neil Young', 'Vocals')
  192.                              ),
  193.             review_table_type( review_type('John', '18-Feb-2019', 'I absolutely LOVE this CD!', 5),
  194.                                review_type('Anthony', '16-Aug-2019', 'Neil Young''s signature album.', 4)
  195.                               ),
  196.                              
  197.             'Audio CD', 1, 4.99, 11.00
  198.          )
  199. )
  200. /
  201. INSERT INTO albums
  202. VALUES(
  203. disk_type('The Essential Bob Dylan', 99, '8-Jul-2016', 'Pop', 26.17, 32,
  204.             artist_array_type( artist_type('Bob Dylan', 'Composer'),
  205.                                artist_type('Bob Dylan', 'Vocals')
  206.                              ),
  207.             review_table_type( review_type('Christopher', '24-Jun-2016', 'This is a terrific album.', 5),
  208.                                review_type('Cauley', '2-Aug-2015', 'There can only be one Bob Dylan. God blessed him with the gift of verse.', 5)
  209.                               ),
  210.             'Audio CD', 2, NULL, 7.00
  211.          )
  212. )
  213. /
  214. INSERT INTO albums
  215. VALUES(
  216. disk_type('Bob Dylan''s Greatest Hits', 50, '1-Jun-1999', 'Pop Rock', 20.81, 10,
  217.             artist_array_type( artist_type('Bob Dylan', 'Composer'),
  218.                                artist_type('Bob Dylan', 'Vocals')
  219.                              ),
  220.             review_table_type( review_type('Kandy', '16-Mar-2015', 'Early Dylan in all his glory.', 5),
  221.                                review_type('Stewart', '18-Feb-2013', 'Captures Bob Dylan transformation from a folk song composer to a rock legend.', 4)
  222.                               ),
  223.             'Audio CD', 1, NULL, 7.00
  224.          )
  225. )
  226. /
  227. INSERT INTO albums
  228. VALUES(
  229. disk_type('Kind Of Blue (Legacy Edition)', 155, '20-Jan-2009', 'Jazz',19.99, 21,
  230.             artist_array_type( artist_type('Miles Davis', 'Composer'),
  231.                                artist_type('Miles Davis', 'Musician')
  232.                              ),
  233.             review_table_type( review_type('Amy', '17-Apr-2018', 'Poor quality sound compared to the vinyl record.', 2)
  234.                               ),
  235.             'Audio CD', 3, 16.99, 10.00
  236.          )
  237. )
  238.  
  239. /
  240. INSERT INTO albums
  241. VALUES(
  242. disk_type('Sketches of Spain', 45, '20-Jan-2009', 'Jazz', 3.11, 6,
  243.             artist_array_type( artist_type('Miles Davis', 'Composer'),
  244.                                artist_type('Miles Davis', 'Musician')
  245.                              ),
  246.             review_table_type( review_type('Sara', '3-Oct-2016', 'Another must have! One of Miles finest works', 5),
  247.                                review_type('Douglas', '14-Jun-2014', 'You might like it, but I admit it seems like a difficult listen.', 5)
  248.                               ),
  249.             'Audio CD', 1, 6.41, 7.00
  250.          )
  251. )
  252. /
  253. INSERT INTO albums
  254. VALUES(
  255. disk_type('Gustav Mahler Symphony No.9 ', 45, '12-Oct-2017', 'Classical', 23.10, 5,
  256.             artist_array_type( artist_type('David Zinman', 'Conductor'),
  257.                                artist_type('Gustav Miller', 'Composer'),
  258.                                artist_type('Tonhalle Orchaestra', 'Orchestra')
  259.                              ),
  260.             review_table_type( review_type('Lindon', '3-Dec-2010', 'This is an uneventful but fine recording.', 3),
  261.                                review_type('Prescott', '24-Aug-2013', 'This is truly a spellbinding record.', 5)
  262.                               ),
  263.             'Audio CD', 1, 15.20, 7.00
  264.          )
  265. )
  266. /
  267. ---MP3 Queries---
  268. INSERT INTO albums
  269. VALUES(
  270. mp3_type('Bob Dylan''s Greatest Hits', 55, '1-Jan-2019', 'Pop Rock', 5.98, 10,
  271.             artist_array_type(
  272.                                artist_type('Bob Dylan', 'Composer'),
  273.                                artist_type('Bob Dylan', 'Vocals')
  274.                              ),
  275.             review_table_type( review_type('Mandy', '16-Mar-2019', 'Fantastic music!.', 5)                              
  276.                               ),
  277.           60
  278.          )
  279. )
  280. /
  281. INSERT INTO albums
  282. VALUES(
  283. mp3_type('Best of Neil Young', 153, '21-Feb-2019', 'Pop Rock', 17.50, 35,
  284.             artist_array_type( artist_type('Neil Young', 'Composer'),
  285.                                artist_type('Neil Young', 'Vocals')
  286.                              ),
  287.             review_table_type( review_type('John', '16-Apr-2019', 'Great artist and great music.', 5)                              
  288.                               ),
  289.           165
  290.          )
  291. )
  292. /
  293. INSERT INTO albums
  294. VALUES(
  295. mp3_type('Harvest (2009 Remaster)', 44, '21-Jun-2009', 'Rock Country', 9.49, 10,
  296.             artist_array_type( artist_type('Neil Young', 'Composer'),
  297.                                artist_type('Neil Young', 'Vocals')
  298.                              ),
  299.             review_table_type( review_type('John', '16-Apr-2019', 'Great artist and great music.', 5)                              
  300.                               ),
  301.           52
  302.          )
  303. )
  304. /
  305. INSERT INTO albums
  306. VALUES(
  307. mp3_type('Sketches of Spain', 45, '16-Aug-2013', 'Jazz', 24.99, 6,
  308.             artist_array_type( artist_type('Miles Davis', 'Composer'),
  309.                                artist_type('Miles Davis', 'Musician')
  310.                              ),
  311.             review_table_type( review_type('Douglas', '14-Jun-2014', 'You might like it but I admit it seems like a difficult listen.', 5)                              
  312.                               ),
  313.           51
  314.          )
  315. )
  316. /
  317. INSERT INTO albums
  318. VALUES(
  319. mp3_type('B.B. King Greatest Hits', 114, '16-Jul-2013', 'Rock Blues', 11.49, 24,
  320.             artist_array_type( artist_type('B.B. King', 'Vocals'),
  321.                                artist_type('B.B. King', 'Guitar')
  322.                              ),
  323.             review_table_type( review_type('David', '18-May-2015', 'You might like it but I admit it seems like a difficult listen.', 5)  
  324.                
  325.                               ),
  326.           125
  327.          )
  328. )
  329. /
  330. INSERT INTO albums
  331. VALUES(
  332. mp3_type('The Essential Bob Dylan', 99, '8-Jul-2016', 'Pop', 16.00, 32,
  333.             artist_array_type( artist_type('Bob Dylan', 'Composer'),
  334.                                artist_type('Bob Dylan', 'Vocals')
  335.                              ),
  336.             review_table_type( review_type('Christopher', '24-Jun-2016', 'This is a terrific album.', 5),
  337.                                     review_type('Cauley', '2-Apr-2015', 'There can only be one Bob Dylan. God blessed him with the gift of verse', 5)
  338.                               ),
  339.           112
  340.          )
  341. )
  342. /
  343. INSERT INTO albums
  344. VALUES(
  345. mp3_type('Other Peoples Lives', 42, '15-Feb-2019', 'Rock Dance', 9.49, 10,
  346.             artist_array_type( artist_type('Stats', 'Composer'),
  347.                                artist_type('Stats', 'Vocals')
  348.                              ),
  349.             review_table_type( review_type('George', '17-Sep-2019', 'Good dancing music.', 3)
  350.                               ),
  351.           45
  352.          )
  353. )
  354. /
  355. --Create ALL_ALBUMS view for Q7 to Q9
  356. CREATE OR REPLACE VIEW all_albums (albumTitle, albumType, albumPrice, discount, albumUsedPrice) AS
  357.     SELECT a.albumTitle, COALESCE(TREAT(VALUE(a) AS Disk_Type).MediaType,'MP3'),
  358.     albumPrice, (albumPrice-a.discountPrice()),
  359.     COALESCE(TREAT(VALUE(a) AS Disk_Type).diskUsedPrice , 0)
  360.     FROM albums a;
  361. /
  362.  
  363. -- Query 1 --
  364. SELECT DISTINCT ALBUMTITLE, ALBUMRELEASEDATE, ALBUMPRICE FROM albums a, TABLE(a.ALBUMARTISTS) c
  365. WHERE
  366. ALBUMRELEASEDATE  > '1-Jan-2015' AND
  367. c.artistName = 'Neil Young'
  368. /
  369. -- Query 2 ---
  370. SELECT DISTINCT albumtitle, c.artistname FROM albums a, TABLE(a.ALBUMARTISTS) c
  371. WHERE VALUE(a) IS OF (MP3_TYPE)
  372. AND
  373. albumtitle NOT IN (
  374. SELECT DISTINCT albumtitle
  375. --,c.artistname
  376. FROM albums a
  377. --, table(a.ALBUMARTISTS) c
  378. WHERE VALUE(a) IS OF (disk_type)
  379. )
  380. ORDER BY ALBUMTITLE
  381. /
  382. -- Query 3 --
  383.  
  384. WITH abc AS     (
  385.                 SELECT albumTitle, AVG(c.reviewScore) AS averageScore FROM albums a, (TABLE(a.ALBUMREVIEWS)) c
  386.                 WHERE VALUE(a) IS OF (MP3_TYPE)
  387.                 HAVING COUNT(c.reviewScore) > 1
  388.                 GROUP BY albumTitle
  389.                 )
  390. SELECT * FROM abc
  391. WHERE averageScore = (SELECT MIN(averageScore) FROM abc)
  392. /
  393. --- Query 4 ---
  394. SELECT albumtitle FROM albums a
  395. WHERE VALUE(a) IS OF (DISK_TYPE)
  396. INTERSECT
  397. SELECT albumtitle FROM albums a
  398. WHERE VALUE(a) IS OF (MP3_TYPE)
  399. ORDER BY albumtitle
  400. /
  401. -- Q5 answer is implemented in the member function section from line 79---
  402.  
  403.  
  404. -- View for Q6 and Q7
  405. CREATE OR REPLACE VIEW all_albums (albumTitle, albumType, albumPrice, discount, albumUsedPrice) AS
  406.     SELECT a.albumTitle, COALESCE(TREAT(VALUE(a) AS Disk_Type).MediaType,'MP3'),
  407.     albumPrice, (albumPrice-a.discountPrice()),
  408.     COALESCE(TREAT(VALUE(a) AS Disk_Type).diskUsedPrice , 0)
  409.     FROM albums a;
  410. /
  411. -- Q6 Select the largest discount
  412. SELECT * FROM all_albums
  413. WHERE discount = (SELECT MAX(discount) FROM all_albums)
  414. /
  415. --- Q7 Select the largest usedPrice
  416. SELECT * FROM all_albums
  417. WHERE albumUsedPrice = (SELECT MAX(albumUsedPrice) FROM all_albums)
  418. /
  419. -- Q8 Select AlbumTitle, ReviewScore that has word great
  420. SELECT DISTINCT albumTitle,  c.reviewText, c.reviewScore FROM albums a, TABLE(a.ALBUMREVIEWS) c
  421. WHERE a.containsText(c.ReviewText, 'great') > 0
  422. /
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top