ITRI 613 - Assignment 2
RDBMS
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).
