Microsoft SQL Server Solutions For Task 2a And 2b

ITDA1001 Database Fundamentals 

6/5/2018 

Student ID: 39696

Student Name: Anita Aniedehe

Report

Task 1

ER Diagram

Figure 1 above shows ER Diagram

Assumptions

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

Task 2 A

Task 2 a (1)

/*Anita Aniedehe. Creating database */

if exists(select * from sys.databases where name = ‘Musica’)

drop database Musica;

create database Musica;

/*Anita Aniedehe. Creating Level table */

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)

/*Anita Aniedehe. Inserting data to Label */

USE Musica;

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

(1, ‘Polydor’, ‘66677xxxx’, ‘NA’, ‘22222’, ‘PP’, ‘USA’),

(2, ‘Rocky’, ‘777666xxxx’, ‘NA’, ‘33333’, ‘RR’, ‘USA’),

(3, ‘Technique’, ‘5555xxxx’, ‘NA’, ‘44444’, ‘TT’, ‘ENGLAND’);

Task 2 a (3)

/*Anita Aniedehe. Select all data from Label */

USE Musica;

select * from Label; 

Task 2 a (4)

/*Anita Aniedehe.. Update Polydor to Australia 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)

/*Anita Aniedehe. 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); 

/*Anita Aniedehe. 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)

/*Anita Aniedehe. Insert into table Release */

USE Musica;

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

(1, ‘MM’, ‘2018-01-01’, 1),

(2, ‘RR’, ‘2018-01-01’, 2),

(3, ‘DD’, ‘2018-01-01’, 3),

(4, ‘EE’, ‘2018-01-01’,1),

(5, ‘CC’, ‘2018-01-01’, 2),

(6, ‘RR’, ‘2018-01-01’,3);

/*Anita Aniedehe. 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)

/*Anita Aniedehe. Delete from table Label */

USE Musica;

Delete from Label;

Task 2 a (8)

/*Anita Aniedehe. Display all Label names, and 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;

Task 2 B

Task 2 b (1)

/*Anita Aniedehe. Creating 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); 

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); 

/*Anita Aniedehe. Adding Constraints to tables*/

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);

sp_rename ‘SongWrittenBy’,’SongDetail’; 

Task 2 b (2)

/*Anita Aniedehe. Insert into SongSungBy table*/

insert into SongSungBy(SongSungById, SongSungByName) values

(1, ‘Sung by Duets’),

(2,’Larger Collection’);

/*Anita Aniedehe. Insert into ArtistType table*/

insert into ArtistType(ArtistTypeId, ArtistTypeName) values

(1, ‘Singers’),

(2,’Writers’);

/*Anita Aniedehe. 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);

/*Anita Aniedehe. 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’);

/*Anita Aniedehe. 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’);

/*Anita Aniedehe. 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)

/*Anita Aniedehe. 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))

/*Anita Aniedehe. 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)

/*Anita Aniedehe. 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)

/*Anita Aniedehe. 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)

/*Anita Aniedehe. 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’;

Task 3

Task 3 (1)

Legal Issues in Database

Database stores a lot of information. Some of this information are confidential and requires only one user to be authorized to access such information. If this information are leak to an authorized individual, the owner of this data may lose his or her assets or may lead to fraud. Database security is one of the key factor that must be implemented to protect data for individuals. Even the person maintaining data does not have authority to access personal information from database [1]. 

Database stores information in different formats. Some are stored as encrypted text which are not readable to human eye. This format protect personal data from anyone accessing this private information without being authorized. Although not all the database stores information in encrypted format, some are being stored as plain text. So storing sensitive information such as credit card information will pose security threats. In case the database is hacked, personal information that are in plain text will easy to be used after retrieval. Unlike the encrypted data which requires keys to be decrypted to plain text format [1]. The main purpose of ensuring database security is to protect data integrity, confidentiality and integrity in every organization.

The following are methods to safeguard database against any malicious attack or unauthorized access include [1]:

·         Securing database communications. This involves security between front end application and database. The communication will be protected by using digital certificate which encrypt all the communication between application and database.

·         Physical database security. The place where database resides must be isolated, guarded by lock and security cameras. Every access must be done through the company policy rules

·         Encryption. All the sensitive information must be encrypted for example password and customer’s credit card information.

·         Access control and permissions. All the users must be manage through centralize active directory or LDAP server which have advanced access control and management of permissions.

·         Identifying users through front application must be implemented to ensure that only users access specific type of data [1].

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[2]:

·         Saves Time. It is easy to analyze the requirements and the relationship that exist between entities and attributes.

·         Gives better visual representation. The ERD gives a logical representation of data hence easy to understand the need before implementations.

·         It ease the 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.

References

[1]”Database Legal Protection (BitLaw)”, Bitlaw.com, 2018. 

[2]”Web Development Blog: What is ERD and Why Is It Important In Website Development?”, Superiorwebsys.com, 2018.