Database Management System: Entity Relationship Diagram, SQL Queries, Legal Issues and Security Techniques

This report covers Entity Relationship Diagram, SQL Queries, Legal Issues and Security Techniques in Database Management System. It includes SQL queries for creating tables, inserting data and displaying records. It also discusses legal issues and security techniques to secure the database. The report concludes with the benefits of ER Diagram in database designing.

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;

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

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

Task 3 (1)
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.

Task 3 (2)
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:
http://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:
http://searchcrm.techtarget.com/definition/entity-relationship-diagram / [Accessed 25
May 2018].