Description
Relational Database Management System (RDBMS) is an advanced version of a DBMS system.
It came into existence during 1970’s. RDBMS system also allows the organization to access
data more efficiently than DBMS. One of the most important features of a RDBMS is the ability
to support multiple users whereas the DBMS only supports one user at a time.
Most companies today have switched from using DBMS to use RDBMS because of its
advanced capabilities and its abilities to help business handle data and manage information by
storing it in the form tables.
PART 1
For the first part of the assignment, Outline the any five RDBMS packages of your choosing
and discuss their unique features and characteristics and also explains how they are better than
traditional DBMS packages.
PART 2
Given the MusicDB Schema below for a popular music streaming application. Refer to the
schema and answer the following questions.
1. Write an SQL query to display all attributes from Genre table.
2. Write an SQL query to drop the table Artists from the DB.
3. Write an SQL query to create the table AlbumSales which will have attributes (ArtistId
INT, AlbumId INT, NumberofSales INT, Genre VARCHAR).
4. Write an SQL query to return all albums which were released in year 2020 from the
table Albums (i.e Where attribute DateReleased is of year 2020).
5. Write an SQL query to create the table Albums but exclude the attribute GenreId and
only have ArtistId as a primary key. Also explains what will happen to the Table Genre if
this is the case.
6. Using the above schema provide an example scenario in which an overlapping
constraint may be experienced. (Hint – Create additional tables named Singles,
ExtendedPlay)
7. Write SQL query for the whole schema and include the tables AlbumSales created
above in question 3. Also include an additional entities named ExtendedPlay which has
attributes (ArtistId INT, EPId INT, DateReleased DATETIME, Genre VARCHAR).
8. Suppose there was an additional entity named RecordSales which had attributes such
as (ArtistId INT, AlbumId INT, NumberofSales INT, Genre VARCHAR, GoldStatus
VARCHAR, PlatinumStatus VARCHAR) what kind of relationship type will it have with the
table Albums?
9. Create a View which displays all attributes from the table Artists?
10. List all the 1 to 1 and 1 to many relationships from the schema with all the additional
tables added (i.e RecordSales, ExtendedPlay, Singles).