Creating And Manipulating A Database For Music Industry

Creating Tables for Labels, Releases, Songs, Artists, Artist Types, and Song Details

1. Data auditing and validation will be done on the application level for example entry date and modification date of each data in the database

/*Muhammad Usama Waseem. Creating database */

create database Musica; 

/*Muhammad Usama Waseem. Creating table Label */

USE Musica;

create table Label(LabelId int NOT NULL primary key, LabelName varchar(100) NOT NULL, Phone  varchar(15) NOT NULL, Street varchar(50) NOT NULL, PostalCode varchar(20), City varchar(50) NOT NULL, Country varchar(50) NOT NULL); 

Task 2 a (2)

/*Muhammad Usama Waseem. Creating table Label */

USE Musica;

Insert into Label(LabelId, LabelName, Phone, Street, PostalCode, City, Country) values

(1, ‘Polydor’, ‘+457832133222’, ‘N/A’, ‘23456’, ‘NY’, ‘USA’),

(2, ‘Rocky’, ‘+427832133222’, ‘N/A’, ‘53456’, ‘ZK’, ‘UK’),

(3, ‘Technique’, ‘+447832133222’, ‘N/A’, ‘33456’, ‘ZK’, ‘SPAIN’); 

Task 2 a (3)

/*Muhammad Usama Waseem. Select details of table Label */

USE Musica;

select * from Label; 

Task 2 a (4)

/*Muhammad Usama Waseem. Update Polydor to Asterial and select Label Name for all austalian for table Label */

USE Musica;

Update Label set Country = ‘Australia’ where LabelName = ‘Polydor’;

Select LabelName from Label Where Country = ‘Australia’; 

Task 2 a (5)

/*Muhammad Usama Waseem. Creating table Release */

USE Musica;

create table Release(ReleaseId int NOT NuLL primary key, ReleaseTitle varchar(100) NOT NULL, ReleaseDate date NOT NULL, LabelId int NOT NULL);

/*Muhammad Usama Waseem. Add foreign key to Release Table from Label table */

USE Musica;

alter table Release add Foreign Key(LabelId) references Label(LabelId);

Task 2 a (6) 

/*Muhammad Usama Waseem. Insert into table Release */

USE Musica;

Insert into Release(ReleaseId, ReleaseTitle, ReleaseDate, LabelId) values

(1, ‘Maze -M’, ‘2018-01-01’, 1),

(2, ‘Regea -R’, ‘2018-01-02’, 2),

(3, ‘Deep House -H’, ‘2018-01-03’, 3),

(4, ‘Electro -E’, ‘2018-01-05’,1),

(5, ‘Country -C’, ‘2018-01-07’, 2),

(6, ‘RNB -R’, ‘2018-01-11’,3);

/*Muhammad Usama Waseem. Select where title start from M-Z from table Release */

USE Musica;

select ReleaseTitle, ReleaseDate from Release where ReleaseTitle LIKE ‘[M-Z]%’;

Task 2 a (7)

/*Muhammad Usama Waseem. Trying to delete from table Label */

USE Musica;

Delete from Label;

Task 2 a (8)

/*Muhammad Usama Waseem. Display all Label names, along with all of their releases, grouped by Label */

USE Musica;

Select LabelName, ReleaseTitle from Label inner join Release on Release.LabelId = Release.LabelId Group by Label.LabelName; 

/*Muhammad Usama Waseem. Display all Label names, along with all of their releases, without grouped by Label */

USE Musica;

Select LabelName, ReleaseTitle from Label inner join Release on Release.LabelId = Release.LabelId; 

Task 2 b (1)

/*Muhammad Usama Waseem. Creating all the tables*/

USE Musica;

create table Song(SongId int NOT NULL primary key, SongName varchar(60) NOT NULL, Length decimal(10, 2));

create table SongWrittenBy(SongWrittenById int NOT NULL primary key, SongId int NOT NULL, ArtistId int NOT NULL, SongSungById int NOT NULL, LabelId int NOT NULL, ArtistTypeId int not null); 

create table Artist(ArtistId int NOT NULL primary key, FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL, Gender varchar(6) NOT NULL, DOB date Not Null, Nationality varchar(50) NOT NULL, ArtistTypeId int NOT NULL); 

Create table ArtistType(ArtistTypeId int NOT NULL primary key, ArtistTypeName varchar(30) NOT NULL);

Create table SongSungBy(SongSungById int NOT NULL primary key, SongSungByName varchar(60) NOT NULL); 

/*Muhammad Usama Waseem. Adding tables relationship where applicable in the database*/

alter table SongWrittenBy add foreign key(SongId) references Song(SongId);

alter table SongWrittenBy add foreign key(ArtistId) references Artist(ArtistId);

alter table SongWrittenBy add foreign key(SongSungById) references SongSungBy(SongSungById);

alter table SongWrittenBy add foreign key(labelId) references Label(LabelId);

alter table SongWrittenBy add foreign key(ArtistTypeId) references ArtistType(ArtistTypeId);

Task 2 b (2)

/*Muhammad Usama Waseem. Insert into SongSungBy table*/

insert into SongSungBy(SongSungById, SongSungByName) values

(1, ‘Sung by Duets’), 

(2,’Larger Collection’);

/*Muhammad Usama Waseem. Insert into ArtistType table*/

insert into ArtistType(ArtistTypeId, ArtistTypeName) values

(1, ‘Singers’), 

(2,’Writers’);

/*Muhammad Usama Waseem. Insert into Song table*/

insert into song(SongId, SongName, Length)values

(1, ‘Shape of You’, 0),

(2, ‘Atlantic City’, 3.57),

(3, ‘Atlantic City’, 2.58),

(4, ‘I’m Waiting for The Man’, 4.38),

(5, ‘Life on Mars’, 3.52);

/*Muhammad Usama Waseem. Insert into Label table*/

Insert into Label(LabelId, LabelName, Phone, Street, PostalCode, City, Country) values

(4, ‘Planet’, ‘+957832133222’, ‘N/A’, ‘93456’, ‘PL’, ‘USA’),

(5, ‘Columbia’, ‘+827832133222’, ‘N/A’, ‘83456’, ‘PL’, ‘UK’);

/*Muhammad Usama Waseem. Insert into artists table*/

insert into artist(ArtistId, FirstName, LastName, Gender, DOB, Nationality) values

(1, ‘Ed’, ‘Sheeran’, ‘M’, ‘1978-07-01’, ‘UK’),

(2, ‘Bruce’, ‘Springsteen’,’M’, ‘1989-03-01’, ‘USA’),

(3, ‘David’, ‘Bowie’, ‘M’, ‘1980-03-01’, ‘AUSTRALIA’),

(4, ‘Lou’, ‘Reed’, ‘M’, ‘1977-07-01’, ‘COLUMBIA’);

/*Muhammad Usama Waseem. Inserting into SongDetail table*/

insert into SongDetail(SongWrittenById, SongId, ArtistId, SongSungById, LabelId, ArtistTypeId)values

(1, 1, 1, 1, 4, 1),

(2, 1, 1, 1, 4, 2),

(3, 2, 2, 1, 4, 1),

(4, 2, 1, 1, 4, 2),

(5, 3, 2, 1, 5, 1),

(6, 3, 2, 1, 5, 2),

(7, 4, 3, 1, 5, 1),

(8, 4, 4, 1, 5, 2),

(9, 5, 3, 1, 5,1),

(10, 5, 3, 1, 5, 2);

Task 2 b (3)

/*Muhammad Usama Waseem. Selecting writers with names table*/

select FirstName, LastName, SongName from Artist inner join SongDetail on 

SongDetail.ArtistId = Artist.ArtistId INNER JOIN Song on Song.SongId = SongDetail.SongId 

INNER JOIN ArtistType on ArtistType.ArtistTypeId = SongDetail.ArtistTypeId 

where ArtistType.ArtistTypeName = ‘Writers’;

Task 2 b (4))

/*Muhammad Usama Waseem. Selecting  name and release table*/

select Release.ReleaseTitle, Artist.FirstName, Artist.LastName, Label.LabelName from 

Release inner join Label ON Label.LabelId = Release.LabelId INNER JOIN SongDetail ON 

SongDetail.LabelId = Label.LabelId INNER JOIN Artist ON Artist.ArtistId = SongDetail.ArtistId;

Task 2 b (5)

/*Muhammad Usama Waseem. Selecting  artist not written songs*/

select * from Artist INNER JOIN SongDetail on SongDetail.ArtistId = Artist.ArtistId INNER JOIN 

ArtistType ON ArtistType.ArtistTypeId = SongDetail.ArtistTypeId where 

SongDetail.ArtistId NOT IN (Select SongDetail.ArtistId FROM 

SongDetail INNER JOIN ArtistType ON ArtistType.ArtistTypeId = SongDetail.ArtistTypeId 

WHERE ArtistType.ArtistTypeId = 2); 

Task 2 b (6)

/*Muhammad Usama Waseem. Selecting  artist written and sung atleast one songs*/

select Artist.FirstName, Artist.LastName from Artist INNER JOIN SongDetail on 

SongDetail.ArtistId = Artist.ArtistId INNER JOIN 

ArtistType ON ArtistType.ArtistTypeId = SongDetail.ArtistTypeId where 

SongDetail.ArtistId IN (Select SongDetail.ArtistId FROM 

SongDetail INNER JOIN ArtistType ON ArtistType.ArtistTypeId = SongDetail.ArtistTypeId 

WHERE ArtistType.ArtistTypeId = 2); 

Task 2 b (7)

/*Muhammad Usama Waseem. Selecting total length of the songs sung by David Bowie*/

select SUM(Song.Length) from song inner join SongDetail on SongDetail.SongId = Song.SongId 

INNER JOIN Artist ON Artist.ArtistId = SongDetail.ArtistId INNER JOIN ArtistType ON 

ArtistType.ArtistTypeId = SongDetail.ArtistTypeId WHERE Artist.ArtistId = 3 AND 

ArtistType.ArtistTypeName = ‘Singers’;

(“Basic Database Security: Step by Step – Information Security Magazine”, 2018) Databases stores organization’s information which are most important. This information consist of confidential and public information. So in order to separate the accessibility of private and public information, a database access rights need to be defined in application level or database level.  (“What is Database Security? – Definition from Techopedia”, 2018) An organization storing customer data must ensures that data are safeguarded from unauthorized access. The potential danger of customer data are fraud which someone can lose money or data infiltration which may results in damage of privacy level.

They are several techniques which can be used to secure database data include  (“5 Key Steps to Ensuring Database Security”, 2018):

• Isolating sensitive data. This involves analyzing data which are being stored in database. Any sensitive information are defined and standard rules which requires auditing and role accessibility are defined.
• Eliminate Vulnerabilities. System administrator must ensures that the operating system, hardware and the database application is up to date. Also regularly vendor’s advices and hot fix or patch must be applied accordingly.
• Data encryption. This involves encrypting confidential information using standard and secured algorithm. Data such as username, email and password must be encrypted in the database.
• Privileges enforcement. A least enforcement must be defined in the database this include roles such as read, write, or modify an existing data. By defining this all the users accessing data will only perform database activity according to their role and permission.
• Database Utilities. This involves tools for monitoring logs. Through monitoring database health can be establish through error generation reports. Also the hardware storage disk health can be known through the database cluster write fails. This will enable administrator to offer quick solution.
• Identifying users through front application must be implemented to ensure that only users access specific type of data
• Enforcing Strong Password.All the users account must be centrally managed and a policy of using strong and expiry period of every password must be set in any organization.
• Lock unused accounts. The unused accounts must be disable in the database. Also default account that shipped with the database must be remove or disabled.

Task 3 (2)

Graphical representation of data aid in representing relational databases. It gives ability to both the client and developer to understand the need of an application. The following are the benefits of using Entity relational diagram to design relational database (“Advantages and Disadvantages of ER Model in DBMS – EDUGRABS”, 2018):

• Visual representation. The ERD gives better logical representation of data hence easy to understand the need before implementations.
• Conversion of data model. ERD helps to convert data model into objects hence developers can integrate easily to object oriented design.
• Simple. The data conceptual is because the relationship between entities and relationship are easy to draw.
• Cheap. Since ERD is easy to create, any user can analyze the requirements and the relationship that exist between entities and attributes.
• Effective communication Tool. ERD is suitable for users and experts since it is a high level of implementing entities relationships.

References

“Database Legal Protection (BitLaw)”, Bitlaw.com, 2018. [Online]. Available: https://www.bitlaw.com/copyright/database.html. [Accessed: 05- Jun- 2018].

“Web Development Blog: What is ERD and Why Is It Important In Website Development?”, Superiorwebsys.com, 2018. [Online]. Available: https://www.superiorwebsys.com/58-what-is-erd-and-why-is-it-important-in-website-development/. [Accessed: 05- Jun- 2018].

Advantages and Disadvantages of ER Model in DBMS – EDUGRABS. (2018). Retrieved from https://www.edugrabs.com/advantages-and-disadvantages-of-er-model/

5 Key Steps to Ensuring Database Security. (2018). Retrieved from https://www.dbta.com/Editorial/Think-About-It/5-Key-Steps-to-Ensuring-Database-Security-95307.aspx

What is Database Security? – Definition from Techopedia. (2018). Retrieved from https://www.techopedia.com/definition/29841/database-security

Basic Database Security: Step by Step – Information Security Magazine. (2018). Retrieved from https://searchsecurity.techtarget.com/magazineContent/Basic-Database-Security-Step-by-Step