Soompi Music

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
Soompi Music Management ERD
4
One to Many Relationship
SM_GroupDetails
SM_Artist
GroupDetailsId GroupName YearDebuted GroupSize man
agementid
ArtistId ArtistStageName ArtistRealName DOB PlaceO
fBirth Gender GroupName ManagementId

• One Group can contain many artists.

5
Many to Many Relationship
SM_GroupDetails
SM_Artist
GroupDetailsId GroupName YearDebuted GroupSize Man
agementId
ArtistId ArtistStageName ArtistRealName DOB PlaceO
fBirth 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.Arti
  stId SM_Artist.ArtistId GROUP BY
  artiststagename having count()gt1

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 gt (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 gt 2),
• Check if album number is a positive integer
• CONSTRAINT SM_AlbumDetails_AlbumNumber CHECK
  (AlbumNumber gt 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 gt 1900) AND (DateFormed lt 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 gt 1900) AND (DATE lt
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)