MBEYA UNIVERSITY OF SCIENCE AND TECHNOLOGY.

COLLEGE OF INFORMATION AND COMMUNICATION TECHNOLOGY

DEPATIMENT: COMPUTER SCIENCE

COURSE NAME: Database Management Systems

COURSE CODE: CS 6261

PARTICIPANTS;

S/N NAME REG NO

1 FREDRICK MWITA 22100523140094
2 GODBLESS YAGAWA 22100523140101
3 CHRISTINA KAPAYA 22100523140007
4 EMMANUEL BWIRE 22100523140106
5 AINULIWE KIBONA 21100523140053
6 GASLAY MAGASHI 22100523140016
7 NATHAN BENJAMIN 221005231400
TASK:
Database schema.

Consider the following relations about music: Artist Name, Type, Country Where
(Name) is the primary key and the value of attribute Type is either 'PERSON or BAND'
Album Title, Artist, Year, Type, Rating Where (Title, Artist) is the primary key, Artist is
a foreign key referencing relation Artist, the value of Type is either 'STUDIO', LIVE' or '
COMPILATION', and Rating is an integer from 1 to 5. Track list Album Title, Album
Artist, Track No, Track Title, Track Length where (Album Title, Album Artist, Track
No) is the primary key and (Album Title, Album Artist) is a foreign key referencing
relation

Problem 1.

Create database for these schemas and populate it. Table artist and album must each
contain for at this least 5 tuples, and each album tables must consist least 5 tracks in
Track list Do not use NULL. You can http://musicbrainz.org/, http:/ /www.allmusic.com/
and http:/ /www.discogs.com/ as sources for music data.

Problem 2

Write the following 15 queries in SQL.

1) List artists who released a live album and a compilation in the same year.

2) List artists who have only released studio albums.

3) List albums which have a higher rating than every previous album by the same band.
4) List the live albums released by Tanzania artists and having a higher rating than the
average rating of all albums released in the same year.

5) List songs shorter than 2 minutes and 34 seconds from albums rated 4 or 5 stars and
released in the last 20 years. Include the album title and artist name in the output.
6) Find the average total running time of all albums released in the '90s and having at
least 10 tracks. (Assume no track is missing in the track list)

7) List artists who have never released two consecutive studio albums more than four
years apart.

8) List artists who have released more live and compilation albums (together) than studio
ones

9) Assuming that the last track of every album is always present, list albums without
missing tracks and their total running time.

10) Among artists who have released at least 3 studio albums, 2 live albums and one
compilation, list those whose every album is rated no less than 3

. 11) Find the number of US bands whose debut album was rated 5 stars. (Assum that
there 1s only one such album per artist)

12) For every artist, find the percentage of their albums rated less than 3. Return artist
names with a lower first and include in the output as a number between 0 and 100 with 2
decimal digits

13) List artists who released no fewer studio albums than any other artist from the same
country

14) List pairs of albums released by artists of different countries in the same year and
such that one has a higher rating than the other.

15) Sort albums by the ratio (highest first) between their rating and the number of tracks
they consist of. (Assume no track is missing in the track list.

ANSWEARS:
CREATE DATABASE musicdatabase;

-- Create the tables

USE musicdatabase;

CREATE TABLE Artist (

Name VARCHAR (100) PRIMARY KEY,

Type VARCHAR (100) CHECK (Type IN ('PERSON', 'BAND')),

Country VARCHAR (50)

);

CREATE TABLE Album (

Title VARCHAR (100),

Artist VARCHAR (100),

Year INT,

Type VARCHAR (15) CHECK (Type IN ('STUDIO', 'LIVE', 'COMPILATION')),

Rating INT CHECK (Rating BETWEEN 1 AND 5),

PRIMARY KEY (Title, Artist),

FOREIGN KEY (Artist) REFERENCES Artist (Name)

);

CREATE TABLE Tracklist (

AlbumTitle VARCHAR (100),

 AlbumArtist VARCHAR (100),

TrackNo INT,

TrackTitle VARCHAR(100),

TrackLength TIME,

PRIMARY KEY (AlbumTitle, AlbumArtist, TrackNo),

FOREIGN KEY (AlbumTitle, AlbumArtist) REFERENCES Album(Title, Artist)

);

-- Populate the tables with at least 5 tuples for Artist and Album, and at least 5 tracks per
album in Tracklist

-- Insert data into Artist

INSERT INTO Artist (Name, Type, Country) VALUES

('Diamond Platnumz', 'Person', 'Tanzania'),

('Harmonize', 'Person', 'Tanzania'),

('Sauti Sol', 'Band', 'Kenya'),

('Fifth Harmony', 'Band', 'USA'),

('Camila Cabello', 'Person', 'USA'),

('Chris Brown', 'Person', 'USA'),

('Westlife', 'Band', 'Uk');

-- Insert data into Album

INSERT INTO album (Title, Artist, Year, Type, Rating) VALUES

('FOA', 'Diamond Platnumz', 2020, 'Studio', 4),

('AFRO EAST', 'Harmonize', 2021, 'LIVE', 3),

('Midnight Train', 'Sauti Sol', 2020, 'Studio', 2),

('Reflection', 'Fifth Harmony', 2021, 'Compilation', 3),

('Romance', 'Camila Cabello', 2019, 'Studio', 5),

('Indigo', 'Chris Brown', 2019, 'Studio', 5),

('WESTLIFE', 'Westlife', '1999', 'Studio', '5');

-- Inserting Data into Tracklist

INSERT INTO tracklist VALUES('FOA','Diamond Platnumz',1,'Melody','00:02:50');

INSERT INTO tracklist VALUES('FOA','Diamond

Platnumz',2,'Somebody','00:03:17');

INSERT INTO tracklist VALUES('FOA','Diamond Platnumz',3,'Fine','00:03:42');

INSERT INTO tracklist VALUES('FOA','Diamond Platnumz',4,'Mtasubiri','00:03:27');

INSERT INTO tracklist VALUES('FOA','Diamond Platnumz',5,'Loyal','00:03:19');

INSERT INTO tracklist VALUES('AFRO EAST','Harmonize',1,'Bed Room','00:03:21');

INSERT INTO tracklist VALUES('AFRO EAST','Harmonize',2,'Fall in

Love','00:02:50');

INSERT INTO tracklist VALUES('AFRO

EAST','Harmonize',3,'Inanimaliza','00:03:50');

INSERT INTO tracklist VALUES('AFRO EAST','Harmonize',4,'I Miss

You','00:03:23');
INSERT INTO tracklist VALUES('AFRO EAST','Harmonize',5,'Good','00:02:29');

INSERT INTO tracklist VALUES('Midnight Train','Sauti Sol',1,'Intro','00:01:11');

INSERT INTO tracklist VALUES('Midnight Train','Sauti Sol',2,'Insecure','00:03:11');

INSERT INTO tracklist VALUES('Midnight Train','Sauti Sol',8,'Set Me

Free','00:02:32');

INSERT INTO tracklist VALUES('Midnight Train','Sauti Sol',7,'Suzana','00:03:51');

INSERT INTO tracklist VALUES('Midnight Train','Sauti Sol',11,'Sober','00:03:20');

INSERT INTO tracklist VALUES('Reflection','Fifth Harmony',1,'Boss','00:02:52');

INSERT INTO tracklist VALUES('Reflection','Fifth

Harmony',9,'Reflection','00:03:09');

INSERT INTO tracklist VALUES('Reflection','Fifth Harmony',8,'Them Girls Be

Like','00:02:43');

INSERT INTO tracklist VALUES('Reflection','Fifth Harmony',10,'Suga

Mama','00:03:40');

INSERT INTO tracklist VALUES('Reflection','Fifth Harmony',11,'We

Know','00:02:58');

INSERT INTO tracklist VALUES('Romance','Camila Cabello',4,'Senorita','00:03:11');

INSERT INTO tracklist VALUES('Romance','Camila Cabello',5,'Liar','00:03:27');

INSERT INTO tracklist VALUES('Romance','Camila Cabello',9,'Dream Of

You','00:03:30');

INSERT INTO tracklist VALUES('Romance','Camila Cabello',13,'First

Man','00:03:48');

INSERT INTO tracklist VALUES('Romance','Camila Cabello',1,'Shameless','00:03:55');

INSERT INTO tracklist VALUES('Indigo','Chris Brown',1,'Indigo','00:03:13');

INSERT INTO tracklist VALUES('Indigo','Chris Brown',8,'Emerald','00:06:39');

INSERT INTO tracklist VALUES('Indigo','Chris Brown',13,'Natural

Disaster','00:05:03');

INSERT INTO tracklist VALUES('Indigo','Chris Brown',11,'No Guidance','00:04:21');

INSERT INTO tracklist VALUES('Indigo','Chris Brown',5,'Wobble Up','00:03:41');

INSERT INTO tracklist VALUES('WESTLIFE', 'Westlife', '2', 'If I Let You

Go','00:03:42'),

('WESTLIFE', 'Westlife', '8', 'Moments', '00:04:18'),

('WESTLIFE', 'Westlife', '1', 'Swear it again', '00:04:08'),

('WESTLIFE', 'Westlife', '3', 'Flying Without Wings','00:03:37'),

('WESTLIFE', 'Westlife', '4', 'Fool Again', '00:03:55'),

('WESTLIFE', 'Westlife', '5', 'No No', '00:03:14'),

('WESTLIFE', 'Westlife', '6', 'I Don’t Wanna Fight', '00:05:03'),

('WESTLIFE', 'Westlife', '7', 'Change the World', '00:03:11'),

('WESTLIFE', 'Westlife', '9', 'Seasons in the Sun', '00:04:09'),

('WESTLIFE', 'Westlife', '10', 'I Need You', '00:03:48'),

('WESTLIFE', 'Westlife', '11', 'Miss You','00:03:53');

-- Question1
SELECT *

FROM album

WHERE Type !='Studio' AND Year=2021;

-- Question2

SELECT *

FROM album

WHERE Type='Studio';
-- Question3

SELECT *

FROM album WHERE Rating >=3 AND Artist='Fifth Harmony';

-- Question4

SELECT a1.Title,a1.Artist,a1.Year,a1.Rating,b1.Country

FROM album a1

INNER JOIN artist b1 ON b1.Name= a1.Artist

WHERE b1.Country='Tanzania' AND a1.Year = 2021 AND a1.Type='Live' AND

Rating >=2.5;
-- Question5

SELECT a1.Title,a1.Artist,a1.Year,b1.TrackLength

FROM album a1

INNER JOIN tracklist b1

ON a1.Title=b1.AlbumTitle

WHERE b1.TrackLength <= '00:02:34';

-- Question 6

SELECT AVG(TotalRunningTime) AS AvgRunningTime

FROM (

SELECT a.Title, a.Artist, SUM(t.TrackLength) AS TotalRunningTime

FROM Album a

JOIN Tracklist t ON a.Title = t.AlbumTitle AND a.Artist = t.AlbumArtist

WHERE a.Year BETWEEN 1990 AND 1999

GROUP BY a.Title, a.Artist

HAVING COUNT(t.TrackNo) >= 10

) AS AlbumRunningTimes;

-- QUestion 7

SELECT Artist

FROM (

SELECT Artist, Title, Year, LAG(Year) OVER (PARTITION BY Artist ORDER

BY Year) AS PrevYear

FROM Album

WHERE Type = 'STUDIO'

) AS StudioAlbums
GROUP BY Artist

HAVING MAX(Year - PrevYear) <= 4 OR COUNT(PrevYear) = 0;

-- Question8

SELECT Artist

FROM Album

GROUP BY Artist

HAVING SUM(CASE WHEN Type IN ('LIVE', 'COMPILATION') THEN 1 ELSE 0

END) >

SUM(CASE WHEN Type = 'STUDIO' THEN 1 ELSE 0 END);

-- Question 9

SELECT t.AlbumTitle, t.AlbumArtist, SUM(t.TrackLength) AS TotalRunningTime

FROM Tracklist t

JOIN (

SELECT AlbumTitle, AlbumArtist, MAX(TrackNo) AS MaxTrackNo

FROM Tracklist

GROUP BY AlbumTitle, AlbumArtist

) AS LastTracks ON t.AlbumTitle = LastTracks.AlbumTitle AND t.AlbumArtist =

LastTracks.AlbumArtist

GROUP BY t.AlbumTitle, t.AlbumArtist

HAVING COUNT(t.TrackNo) = MAX(LastTracks.MaxTrackNo);

-- Question11

SELECT COUNT(DISTINCT a.Artist) AS NumberOfBands

FROM Album a

JOIN Artist ar ON a.Artist = ar.Name

WHERE ar.Country = 'USA' AND ar.Type = 'BAND' AND a.Rating = 5

AND a.Year = (

SELECT MIN(Year)

FROM Album

WHERE Artist = a.Artist

);
-- Question 12

SELECT Artist,

ROUND(100.0 * SUM(CASE WHEN Rating < 3 THEN 1 ELSE 0 END) /

COUNT(*), 2) AS Percentage

FROM Album

GROUP BY Artist;
-- Question 13

WITH StudioAlbumCounts AS (

SELECT a.Artist, ar.Country, COUNT(*) AS StudioAlbumCount

FROM Album a

JOIN Artist ar ON a.Artist = ar.Name

WHERE a.Type = 'STUDIO'

GROUP BY a.Artist, ar.Country

SELECT sac.Artist

FROM StudioAlbumCounts sac

WHERE sac.StudioAlbumCount = (

SELECT MAX(sac2.StudioAlbumCount)
 FROM StudioAlbumCounts sac2

WHERE sac2.Country = sac.Country

);

-- Question 14

SELECT a1.Title AS Album1, a1.Artist AS Artist1, a2.Title AS Album2, a2.Artist AS

Artist2, a1.Year

FROM Album a1

JOIN Album a2 ON a1.Year = a2.Year AND a1.Rating > a2.Rating

JOIN Artist ar1 ON a1.Artist = ar1.Name

JOIN Artist ar2 ON a2.Artist = ar2.Name

WHERE ar1.Country != ar2.Country;

-- Question 15

SELECT a.Title, a.Artist, a.Rating, COUNT(t.TrackNo) AS TrackCount,

a.Rating * 1.0 / COUNT(t.TrackNo) AS RatingPerTrack

FROM Album a

JOIN Tracklist t ON a.Title = t.AlbumTitle AND a.Artist = t.AlbumArtist

GROUP BY a.Title, a.Artist, a.Rating

ORDER BY RatingPerTrack DESC;