Analysis of Database Systems - Essay Example

After reading the case study for Notown Records in their decision to set up a database to maintain and manage important records relating to their day-to-day activities. The EER model for the database is as follows:

• The primary key of the Musician relation is the “Employee_Number” which has been used to uniquely identify the musicians.
• “Name” and “Musical Key” together form the primary key of Instruments relation.
• “Author” and “Title” form the primary key of Song relation.
• “Identifier” forms the primary key of Album relation.
• The relationship “play” between the entities Musician and Instrument is M: N as more than 1 musician can play multiple instruments.
• The relationship “performed” between the entities Musician and song are M: N as more than 1 song can be performed by multiple musicians.
• The relationship “arranged” between the entities Elite Musician and Song is N: 1 as one song can be arranged by many musicians whereas the same song cannot be arranged by more than one elite musician.
• The relationship “has’ between the entity's Album and Song is 1: N as songs in one album cannot be there in the other albums.
• The relationship “produced_by” between the entities Elite Musician and Album is 1: N as many albums can be produced by one elite musician.
• The attribute “Can_they_solo” and “Can_they_band” takes only true and false values.

Relational Model with 3-NF done:

Figure 2: Relational Model

SQL queries:

1. Regular entry of new albums and new songs
   1. Entry of new albums

INSERT INTO Albums (Employee_Number, Identifier, Title, Copyright_date, Format) VALUES (103789, “Jazz56”, “New Horizons”, 23-10-07, “CD”);

INSERT INTO Albums (Employee_Number, Identifier, Title, Copyright_date, Format) VALUES (103735, “Metal01”, “Life is Beautiful”, 04-06-06, “Vinyl”);

1. Entry of new songs

INSERT INTO Songs (Author, Title, Identifier) VALUES (“Harry”, “Everything is fine”, “Metal01”);

INSERT INTO Songs (Author, Title, Identifier) VALUES (“Rush dam”, “Here am I”, “Jazz56”);

2. Monthly reports on the arrangements by each elite musician

SELECT M.Employee_Number, M.Gold_albums, M.Platinum_albums, M.Can_lead_bands, M.Author, M.Title

FROM Elite_Musician M, Song S

WHERE S.Author = M.Author AND S.Title = M.Title

GROUP BY M.Employee_Number;

3. Intermittent entry and maintenance of sessional musicians

INSERT INTO Sessional_Musicians (Employee_number, Preffered_musical_genre, Band_size, Can_they_solo) VALUES (103456, “Dance”, 5, “Yes”);

INSERT INTO Sessional_Musicians (Employee_number, Preffered_musical_genre, Band_size, Can_they_solo) VALUES (103675, “Metal”, 7, “No”);

4. Reports detailing the number of sessional musicians by genre

SELECT COUNT (SM.Employee_Number) “NO OF MUSICIANS”,  SM.Preferred_musical_genre

FROM  Sessional_Musicians SM

GROUP BY  SM.Preffered_musical_genre

5. Monthly records on albums produced that month

SELECT  A.Employee_Number, A.Identifier, A.Title, A.Copyright_date, A.Format

FROM Album A

WHERE  IS_CHAR (A.Copyright_date, “dd-mm-yy” ) LIKE “*-JUL-*”;

6. Which musician produced an album named “Life is Beautiful”

SELECT M.Name, A.Title

FROM  Elite_Musician EM, Album A, Musician M

WHERE  EM.Employee_Number = M.Employee_Number AND EM.Employee_Number = A.Employee_Number;

Circumstances where Supertype and Subtype Entities are used in database modeling

When one entity or relation can be broken into more than one form which forms a part of the super relation and includes all the attributes of the super relation, they are called specialization.

In our example, Notown Records case study, we witness that musicians can be segregated into elite and sessional and they individually possess several characteristics which are distinct from each other. They are made into sub classes of the super class Musician.  They form a part of the relation but are identified with various attributes. Partitioning the distinguishable two categories into sub types has reduced redundancy and anomalies of insertion, modification, and deletion.