ITDA1001 - Database Design and Implementation: Musica Case Study

This report presents a database design and implementation for the Musica case study, involving the creation of an Entity Relationship Diagram (ERD) and SQL code to manage music-related data for Fringe Dweller's streaming music website. The database schema includes tables for Labels, Releases, Artists, ArtistTypes, and ArtistReleases, capturing relationships between songs, artists (singers and writers), and releases. SQL queries are provided for creating tables, inserting data, updating records, and retrieving information such as artists who are both singers and writers, song lengths sung by specific artists, and releases associated with particular labels. The report also addresses legal issues related to database security, highlighting the importance of data encryption, authentication, and authorization techniques to protect sensitive customer information. The benefits of using ER diagrams for database design are discussed, emphasizing their role in providing a clear blueprint for database implementation.

Report
Task 1
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.

Task 2a
Task 2a (1)
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)
);

Task 2a (2)
/* 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');
Task 2a (3)
/* Rajat Patel. Q 2 a. (3) display Label Detail */
select * from Label;

Task 2a (4)
/* 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';

Task 2a (5)
/* 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)
);

Task 2a (6)
/* 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;

Task 2a (7)
/* 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;
Task 2a (8)
Select LabelName, count(Release.LabelID) NumOfReleases from Label inner join
Release on Label.LabelID =Release.LabelID
group by LabelName;

Task 2b
Task 2b (1)
/* 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)
);

Task 2b (2)
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);

Task 2b (3)
/* Rajat Patel. Q 2 b. (3) display writers */
select ArtistName from Artist where ArtistID in (select ArtistID from
ArtistType where ArtistType='Writer');
Task 2b (4)
/* 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;