Eventually, the database will need to keep track of downloads and payments, but at this early stage of development the tracking part of the system is being ignored. For now, Ms Dweller would simply like you to build a database that stores details about all the songs in her collection. This is what you know:
• The database will store information about songs and artists 
• The owner has instructed you to keep track of 2 types of artists for each song – singers and writers. 
• You have noted that you will need to account for these facts: o Some songs are sung by duets or a larger collection of singers
o A song can be written by more than one person
o Different versions of the same song can appear over the years. Ms Dweller calls each version a “release”.
• Ms Dweller said it’s important to store information about each release’s Recording Label too. The Label is often the copyright owner and the song distributor. At this stage, you will store the Label’s name and contact details and will keep track of all songs in your database that it has released
• Ms Dweller wants to keep track of each artist's name, date of birth, gender, and nationality. She wants it to be possible to search for details about each release’s title, writer(s), singer(s), length, release date, and Label.

Answer:

Entity Relationship Diagram

(Oracle. 2000)

(TechTarget. 2016)

Assumptions

1. A label’s address is necessary to be stored into the database.
2. A release must contain a label.
3. An artist may be singer, writer or both.
4. create database Musica;

use Musica; 

CREATE TABLE Label (

    LabelID int NOT NULL,

    LabelName nvarchar(30) NOT NULL,

    Street nvarchar(50) NOT NULL,

    [State] nvarchar(10) NOT NULL,

    Country varchar(20) NOT NULL,

    PRIMARY KEY (LabelID)

);

/* Rajat Patel. Q 2 a. (2) insert labels */

insert into Label(LabelID, LabelName, Street, [State], Country)

values (101,'Inxs','12 White Square','QLD', 'Australia'),

(102,'IceHouse','2 George Street','NJ','USA'),

(103,'Polydor','2 Church Street','NJ', 'USA');

/* Rajat Patel. Q 2 a. (3) display Label Detail */

select * from Label;

/* Rajat Patel. Q 2 a. (4) Change country of Polydor label and show all Austalian labels */

update Label set Country='Australia' where LabelName='Polydor';

select LabelName from Label where Country='Australia';

/* Rajat Patel. Q 2 a. (5) Create Release table */

CREATE TABLE Release (

    ReleaseID int NOT NULL,

    ReleaseTitle varchar(50) NOT NULL,

    [Length] varchar(10) NOT NULL,

    ReleaseDate int NOT NULL,

    LabelID int NOT NULL,

    PRIMARY KEY (ReleaseID),

    CONSTRAINT FK_Label FOREIGN KEY (LabelID)

    REFERENCES Label(LabelID)    

);

/* Rajat Patel. Q 2 a. (6) Insert data into Release table and show half records */

insert into Release (ReleaseID, ReleaseTitle, [Length], ReleaseDate, LabelID)

values (101, 'Truly, Madly, Deeply', '3:56', 1992, 101),

(102, 'Jailbreak ', '3:00', 2010, 102),

(103, 'She’s So Fine', '3:16', 2018, 103),

(104, 'April Sun in Cuba', '3:26', 2009, 101),

(105, 'To Her Door', '3:00', 2001, 102),

(106, 'Breathe Me', '3:04', 2000, 103);

SELECT ReleaseTitle, ReleaseDate FROM Release

WHERE ReleaseTitle LIKE '[A-J]%' COLLATE Latin1_General_BIN;

/* Rajat Patel. Q 2 a. (7) Delete all labels instead of relaeses as Release is the Child table and records will be deleted from Release table */

delete from Label;

Select LabelName, count(Release.LabelID) NumOfReleases from Label inner join Release on Label.LabelID =Release.LabelID

group by LabelName;

/* Rajat Patel. Q 2 b. (1) Create Database */

CREATE TABLE Artist (

    ArtistID int NOT NULL,

    ArtistName varchar(30) NOT NULL,

    Gender varchar(1) NOT NULL,

    Nationality varchar(20) NOT NULL,

    PRIMARY KEY (ArtistID)

);

CREATE TABLE ArtistType (

    ArtistID int NOT NULL,

    ArtistType varchar(10) NOT NULL,

    PRIMARY KEY (ArtistID, ArtistType),

    CONSTRAINT FK_ArtistID FOREIGN KEY (ArtistID)

    REFERENCES Artist(ArtistID),

);

CREATE TABLE ArtistRelease (

    ID int NOT NULL,

    ReleaseID int NOT NULL,

    WriterID int NOT NULL,

    SingerID int NOT NULL,

    PRIMARY KEY (ID),

    CONSTRAINT FK_Artist FOREIGN KEY (WriterID)

    REFERENCES Artist(ArtistID),

    CONSTRAINT FK_Artistsinger FOREIGN KEY (SingerID)

    REFERENCES Artist(ArtistID),

    CONSTRAINT FK_Release FOREIGN KEY (ReleaseID)

    REFERENCES Release(ReleaseID)

);

insert into Label(LabelID, LabelName, Street, [State], Country)

values (104,'Planet','11 George Street','NY', 'USA'),

(105,'Columbia','1 Smith Street','NJ', 'USA' );

insert into Artist (ArtistID, ArtistName, Gender, Nationality)

values (1, 'Ed Sheeran', 'M','American'),

(2, 'Bruce Springsteen', 'M','American'),

(3, 'David Bowie', 'M','American'),

(4, 'Lou Reed', 'M','American'),

(5, 'Joe White','M','American');

insert into ArtistType(ArtistID, ArtistType)

values (1, 'Singer'),

(1, 'Writer'),

(2, 'Singer'),

(2, 'Writer'),

(3, 'Singer'),

(3, 'Writer'),

(4, 'Singer'),

(4, 'Writer'),

(5, 'Singer');

insert into Release (ReleaseID, ReleaseTitle, [Length], ReleaseDate, LabelID)

values (107, 'Shape of You', '-', 2016, 104),

(108, 'Atlantic City', '3:57',2014,104),

(109, 'Atlantic City', '2:58',1982,105),

(110, 'I m Waiting for The Man','4:38',1973,105),

(111,'Life on Mars','3:52',1971,105);

insert into ArtistRelease(ID, ReleaseID, WriterID, SingerID)

values (1, 107, 1,1),

(2, 108, 2, 1),

(3,109,2,2),

(4,110,4,4),

(5,110,4,3),

(6,111,3,3),

(7,101,2,3),

(8,102,2,3),

(9,103,2,3),

(10,104,2,3),

(11,105,4,3),

(12,106,2,3);

/* Rajat Patel. Q 2 b. (3) display writers */

select ArtistName from Artist where ArtistID in (select ArtistID from ArtistType where ArtistType='Writer');

/* Rajat Patel. Q 2 b. (4) display all releases */

SELECT     Release.ReleaseTitle, ArtistType.ArtistType, Artist.ArtistName, Label.LabelName FROM Label INNER JOIN

Release ON Label.LabelID = Release.LabelID INNER JOIN

ArtistRelease ON Release.ReleaseID = ArtistRelease.ReleaseID INNER JOIN

Artist ON ArtistRelease.WriterID = Artist.ArtistID AND ArtistRelease.SingerID = Artist.ArtistID INNER JOIN

ArtistType ON Artist.ArtistID = ArtistType.ArtistID;

/* Rajat Patel. Q 2 b. (5) display all artists who never write a song */

SELECT ArtistName FROM Artist where ArtistID not in ( Select ArtistID from ArtistType where ArtistType='Writer')

/* Rajat Patel. Q 2 b. (6) display all artists who are singer and writer both */

SELECT ArtistName FROM Artist where ArtistID in ( Select ArtistID from ArtistType where ArtistType='Writer')

and ArtistID in ( Select ArtistID from ArtistType where ArtistType='Singer');

/* Rajat Patel. Q 2 b. (7) display song length sung by David Bowie */

SELECT Release.ReleaseTitle, substring(Release.Length,1,1) as [Minute],substring(Release.Length,3,2) as [Second]

FROM Release INNER JOIN

ArtistRelease ON Release.ReleaseID = ArtistRelease.ReleaseID INNER JOIN

Artist ON ArtistRelease.WriterID = Artist.ArtistID AND ArtistRelease.SingerID = Artist.ArtistID INNER JOIN

ArtistType ON Artist.ArtistID = ArtistType.ArtistID where ArtistType.ArtistType='Singer' and Artist.ArtistName='David Bowie';

Legal Issues in Database

There may be many legal issues come in front of the organizations while storing the important details into the database as shown below-

• The important detail of customers like payment detail should be highly confidential and should be secured to a high extent.  If any payment detail gets leaked, it will be trouble for the company.
• The customer’s personnel detail should also be secured otherwise the detail may be shared at social networking sites and it may be trouble for the company.

Database Security Techniques 

There are so many security techniques that can be applied on the database to secure the important data. The database security is the main concern of every organization as without database security no business can grow. Some of the important database security techniques are as follows

• Data encryption/decryption techniques. This technique can be used to store important data into encrypted form and can be retrieved at any time in the decrypted form. It is very beneficial and the cost of it is also very less. The hacker or any malicious software cannot easily crack the encrypted data.
• Database authentication. It is also one of the safest methods to secure the database from unauthorized users. By proper authentication we can prevent the unauthorized users to access the database. A user can access the database only after proper authentication.
• Database authorization. It is also one of the best methods to safely control the database features. An admin can hide the important detail from users by not giving access to users to the particular area of the database.

Benefits of ER Diagram

ER diagram is the most important technique to describe the database in a very simple way. It helps in depicting the entire database at one place and a user can easily understand the complete database by studying the ER Diagram. It shows all the required entities along with their attributes. It shows the relations between the entities also that helps in developing the database.

The ER diagram is the start point of database designing and if the ER Diagram is created accurately, then the database implementation will be successful. ER diagram works as the blueprint for the database design.

It is very effective in spite of textual representation of entities. It shows great impact and easily readable by the users. 

References

Oracle. (2000). Drawing the Entity Relationship Diagram [online]. Available from:  https://docs.oracle.com/cd/A87860_01/doc/java.817/a81358/05_dev1.htm / [Accessed 25 May 2018].

TechTarget. (2016), entity relationship diagram [online]. Available from:  https://searchcrm.techtarget.com/definition/entity-relationship-diagram / [Accessed 25 May 2018].