Database Design and Implementation - Math Problem Example

Database Design and Implementation Insert Name Insert Name of the Course Insert Name of Professor Insert Name of University Insert City, State October 8, 2016 Table of Contents Introduction 2 Relational Design 2 Object-Relational Design and Implementation 4 Design and Create OMDB Types and Tables 4 Design modifications 5 Implementation of Methods 7 OMDB Queries 8 References 12 Introduction The main aim is to design and implementation of an Online Music Database. This database will maintains information about music albums available for purchase from the proposed online music store. It will support the online store website and it allows users to query this information and search for albums. The Albums will include audio CD, vinyl record, audio cassette and downloadable MP3. The price of albums varies depending on the format, e.g. a vinyl record is typically more expensive than an audio CD of the same album. New albums are generally more expensive than used items. In the case of MP3 albums, individual tracks are priced separately and are available for download. The following attributes for albums are used album title, playtime, genre, releaseDate, format, Artists, album price, used price reviews and racks. Relational Design Normalization is done to organize data in a database in an efficient manner. The goal of normalization is to eliminate redundant or duplicate data and to make sure that only related data is stored in a table. There are three entities in Online Music Database; they are album entity, track details entity and review details entity. There are three levels that is album, track and review. Album entity has Title, Playtime, Pric, Genre and release_date while track has track_id, Song_title, song_duration and song_price. The last entity is reviews which has review _ID, reviewer_ name, reviewer_ text, reviewer_ score and reviewer_ date Object-Relational Design and Implementation There are three levels that is album, track and review. Album entity has the following related sets of data: release_date (field type: alphanumeric, length: 8), Title (field type: character, length: 30), Price (field type: numeric, length: 5), Playtime (field type: numeric, length: 5), Genre (field type: numeric, length: 5). For the second entity is track using the following sets of related data, track_id (field type: alphanumeric, length: 8), Song_title (field type: character, length: 30), song_duration (field type: time, length: 5), song_price (field type: date, length: 8). The last entity is reviews where the data is grouped using the following sets of related data, review _ID (field type: alphanumeric, length: 8), reviewer_ name (field type: character, length: 30), reviewer_ text (field type: character, length: 30), reviewer_ score (field type: numeric, length: 5), reviewer_ date (field type: date, length: 8), Design and Create OMDB Types and Tables Test Data: album Album_ID Album title Price release_date Playtime Genre CD001 I love you 25 1st feb 2013 65 blues vinyl 001 dreams 30 21st feb 2014 75 classical cassette 001 Jennifer 18 11th feb 2012 35 rock MP3 001 Jerry 21 1st Mar 2011 185 jazz CD001 West 24 1st May 2013 60 classical Test Data: track_Details Track_Id Song_title song_duration (minutes) song_price Ant001 Baby 6 0.25 Ant002 See u tonight 5 0.15 Pam401 Road ahead 6 0.23 Pam401 New York 10 0.30 Sam402 Joyce 7 0.26 Test Data: reviewer_Details reviewer_Id reviewer_ name reviewer_ text reviewer_ score reviewer_ date review001 John John001 35 4/21/2015 review002 Rose Ros201 55 6/26/2015 review003 David Dav001 63 5/09/2015 review004 Venice Ven32 42 2/19/2016 review005 John John002 52 3/21/2016 Design modifications There are three levels that is album, track and review. Album entity has the following related sets of data: release_date (field type: alphanumeric, length: 8), Title (field type: character, length: 30), Price (field type: numeric, length: 5), Playtime (field type: numeric, length: 5), playlist number (PLAY_NO) (field type: numeric, length: 5). Genre (field type: numeric, length: 5). For the second entity is track using the following sets of related data, track_id (field type: alphanumeric, length: 8), Song_title (field type: character, length: 30), song_duration (field type: time, length: 5), DL_COUNT (field type: numeric, length: 5).song_price (field type: date, length: 8). The last entity is reviews where the data is grouped using the following sets of related data, review _ID (field type: alphanumeric, length: 8), reviewer_ name (field type: character, length: 30), reviewer_ text (field type: character, length: 30), reviewer_ score (field type: numeric, length: 5), reviewer_ date (field type: date, length: 8), Test Data: album Album_ID Album title Price release_date Playtime Genre (PLAY_NO) CD001 I love you 25 1st feb 2013 65 blues 001 vinyl 001 dreams 30 21st feb 2014 75 classical 002 cassette 001 Jennifer 18 11th feb 2012 35 rock 006 MP3 001 Jerry 21 1st Mar 2011 185 jazz 008 CD001 West 24 1st May 2013 60 classical 007 Test Data: track_Details Track_Id Song_title DL_COUNT song_duration (minutes) song_price Ant001 Baby 5 6 0.25 Ant002 See u tonight 0 5 0.15 Pam401 Road ahead 1 6 0.23 Pam401 New York 3 10 0.30 Sam402 Joyce 0 7 0.26 Test Data: reviewer_Details reviewer_Id reviewer_ name reviewer_ text reviewer_ score reviewer_ date review001 John John001 35 4/21/2015 review002 Rose Ros201 55 6/26/2015 review003 David Dav001 63 5/09/2015 review004 Venice Ven32 42 2/19/2016 review005 John John002 52 3/21/2016 Implementation of Methods 6.1 DiscountPrice PL/SQL method DiscountPrice() to implement the following discount rule for new albums: - for all classical albums released in CD format discount the standard Price by 25% - for all jazz albums released on vinyl discount the standard Price by 20% - for all rock albums released on vinyl discount the standard Price by 15% 6.2 AvgReviewScore() SELECT REVIEWER_NAME, AvgReviewScore() FROM Reviews_ Details WHERE Reviewer_ score =(SELECT AVG(Reviewer_ score) FROM Reviews_ Details); OMDB Queries 7.1 Give the release date, standard price, and the conductor for all albums of Tchaikovsky’s Swan Lake released since 1 January 2000. $sql = "SELECT album, release_date Read More