1 / 15

Soompi Music

Soompi Music. By: Karen Sin. Introduction to Soompi Music. This database models from the various artists and group data gathered from the Korean based English forum called ‘Http://www.Soompi.com’.

sutton
Download Presentation

Soompi Music

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Soompi Music By: Karen Sin

  2. Introduction to Soompi Music • This database models from the various artists and group data gathered from the Korean based English forum called ‘Http://www.Soompi.com’. • This database shows the various aspects of artists information that many people have interests in. • It allows users to search up their favourite solo artists or groups and shows fanclubs, the various albums they may have released and their promotional events they have participated in.

  3. SM_FanClubDetails FanClubDetailsId FanClubName ArtistId* GroupDetailsId* YearFormed Colour SM_PromotionalEvents SM_GroupDetails SM_Artist PromotionalEventId ArtistId* GroupDetailsId Location EventType Date GroupDetailsId GroupName YearDebuted GroupSize ManagementId ArtistId ArtistStageName ArtistRealName DOB PlaceOfBirth Gender GroupName ManagementId SM_ArtistPE ArtistId* PromotionalEventId* SM_AlbumDetails AlbumDetailsId ArtistId* GroupDetailsId* AlbumName AlbumNumber AlbumYear Language Soompi Music Management ERD

  4. One to Many Relationship SM_GroupDetails SM_Artist GroupDetailsId GroupName YearDebuted GroupSize managementid ArtistId ArtistStageName ArtistRealName DOB PlaceOfBirth Gender GroupName ManagementId • One Group can contain many artists.

  5. Many to Many Relationship SM_GroupDetails SM_Artist GroupDetailsId GroupName YearDebuted GroupSize ManagementId ArtistId ArtistStageName ArtistRealName DOB PlaceOfBirth Gender GroupName ManagementId Many Artists can have many albums and many albums can contain many groups SM_AlbumDetails AlbumDetailsId ArtistId* GroupDetailsId* AlbumName AlbumNumber AlbumYear Language

  6. Query on a single entity/table Show all the artists that were born at Seoul. SELECT * FROM SM_Artist WHERE PlaceOfBirth = ‘Seoul’; artistid | artiststagename | artistrealname | dob | placeofbirth | gender | groupname | managementid ----------+-----------------+--------------------+------------+--------------+--------+-----------+-------------- 0003 | Micky | Park YooChun | 1986-06-04 | Seoul | M | DBSK | SM3 0005 | Max | Choi Kang Changmin | 1988-02-18 | Seoul | M | DBSK | SM5 (2 rows)

  7. Natural Join Show artist stage name, Group Name and fan club name where gender of an artist is female. SELECT ArtistStageName, GroupName,FanClubName FROM SM_Artist NATURAL JOIN SM_FanclubsDetails WHERE Gender = ‘F’; artiststagename | groupname | fanclubname - - - -- - - - -- - - ----+- - - -- - - - - - - +- - - - - - -- ----- BoA | NULL | Jumping BoA (1 row)

  8. Cross Product Join • SELECT ArtistStageName, GroupName, FanClubsName FROM SM_Artist, SM_FanclubsDetails WHERE SM_Artist.ArtistId = SM_FanclubsDetails.ArtistId and Gender = ‘F’; artiststagename | groupname | fanclubname - - - -- - - - -- - - ----+- - - -- - - - - - - +- - - - - - -- ----- BoA | NULL | Jumping BoA (1 row)

  9. Group By using Having Show the total number of albums released by each group that has more than 1 album released SELECT artiststagename, count(*) AS TotalAlbums FROM SM_GroupDetails , SM_Artist, SM_AlbumDetails WHERE SM_GroupDetails.Groupname = SM_AlbumDetails.Groupname and SM_AlbumDetails.ArtistId = SM_Artist.ArtistId GROUP BY artiststagename having count(*)>1; artiststagename | totalalbums - - - - - - - - - - - - - -+ - - - - - - - - - - --- DBSK | 3 (1 row)

  10. Sub Query Show all the albums with the album number greater than the average for all album numbers SELECT AlbumName, AlbumNumber FROM SM_AlbumDetails WHERE AlbumNumber > (SELECT AVG(AlbumNumber) FROM SM_AlbumDetails); albumname | albumnumber --------------------------------+------------- Miracle | 4 Atlantis Princess | 5 Shine We Are | 6 My Name | 7 Girls On Top | 8 Cao Cao | 4 Utada Hikaru Single Collection | 4 Ultra Blue | 5 (8 rows)

  11. Self Join Show all the albums that were released in the same year as the ‘Rising Sun’ album SELECT n2.albumname, n1.albumyear FROM SM_AlbumDetails n1, SM_AlbumDetails n2 WHERE n1.albumname = 'Rising Sun' and n1.albumyear = n2.albumyear; albumname | albumyear --------------+----------- Rising Sun | 2005 Girls On Top | 2005 No. 89757 | 2005 Cao Cao | 2005 (4 rows)

  12. Check Statements • Check if a group meets the requirement of having 2 or more members CONSTRAINT SM_GroupDetails_GroupSize CHECK ( GroupSize >= 2), • Check if album number is a positive integer CONSTRAINT SM_AlbumDetails_AlbumNumber CHECK (AlbumNumber >= 1),

  13. SQL Action Statements CREATE TABLE SM_FanClubsDetails ( FanClubDetailsId CHAR(3) NOT NULL, FanClubName TEXT, ArtistId CHAR(4), GroupDetailsId CHAR(5), DateFormed CHAR(4), Colour TEXT, --Colour used to represent the fan club CONSTRAINT SM_FanClubsDetails_PK PRIMARY KEY (FanClubDetailsId), CONSTRAINT SM_FanClubsDetails_FK FOREIGN KEY (ArtistId) REFERENCES SM_Artist (ArtistId) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT SM_FanClubsDetails_DateFormed CHECK ((DateFormed >= 1900) AND (DateFormed <= 2007)) ); This is used here so that even if a fan club dismisses it does not mean an artist will be deleted aswell

  14. SQL Action Statements ); CREATE TABLE SM_PromotionalEvents ( PromotionalEventsId Char(4) NOT NULL, ArtistId Char(4), Location TEXT, EventType TEXT, Date DATE, CONSTRAINT SM_PromotionalEvents_PK PRIMARY KEY (PromotionalEventsId), CONSTRAINT SM_PromotionalEvents_FK FOREIGN KEY (ArtistId) REFERENCES SM_Artist ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT SM_PromotionalEvents_Date CHECK ((Date >= 1900) AND (DATE <= 2007)) --Events must of alreadly occurred to be here ); Prevents the deletion of data used by any other groups in other tables.

  15. Creating a View Show every album every artist in the database have released CREATE VIEW SM_EveryArtistandAlbum (ArtistStageName, AlbumName)As SELECT ArtistStageName, AlbumName FROM Sm_artist, Sm_albumdetails where SM_artist.Artistid= SM_albumdetails.artistid; artiststagename | albumname -----------------+-------------------------------- DBSK | Miduhyo DBSK | Rising Sun DBSK | O Jung Ban Hab BoA | Id Peace B BoA | Dont Start Now BoA | No.1 BoA | Miracle BoA | Atlantis Princess BoA | Shine We Are BoA | My Name BoA | Girls On Top JJ Lin | Music Voyage JJ Lin | Haven JJ Lin | No. 89757 JJ Lin | Cao Cao Utada Hikaru | First Love Utada Hikaru | Distance Utada Hikaru | Deep River Utada Hikaru | Utada Hikaru Single Collection Utada Hikaru | Ultra Blue (20 rows)

More Related