Report: Database Design for Music Artist Management System

This report focuses on the database design for a Music-Artist Management System, addressing the limitations of the current manual operations. It encompasses problem statements, conceptual design, and an Enhanced Entity Relationship Diagram (EERD). The report outlines business rules, logical database design using normalized tables, and the implementation in Oracle, including table creation, data population, and the creation of database indexes. The design incorporates entities like Group, Solo Performer, and Artist, with relationships defined to manage instruments, group memberships, and concert-related information. Several SQL queries are provided to demonstrate data retrieval and manipulation. The report concludes with a summary of the database design process and references related to normalization and database design principles. The report aims to provide an automated system that will improve data organization, security, and efficiency.

Abstract
The report is written for Music-Artist Management System and the information on the system
database and its design is included. Problem statement and the database design are covered with
design diagrams. For the diagram, the set of business rules and a few assumptions have been
used. Logical database design is covered using normalized tables and indexes. Database design
in Oracle, data population and SQL queries are included.
1

Table of Contents
Problem scenario....................................................................................................................3
Conceptual design of proposed system...................................................................................3
Enhanced Entity Relationship Diagram (EERD)............................................................................3
Business rules..................................................................................................................................3
Logical database design..........................................................................................................4
Normalized tables...........................................................................................................................4
Database design in Oracle...............................................................................................................5
Database indexes.............................................................................................................................7
Data population...............................................................................................................................8
SQL Query.............................................................................................................................9
Conclusion............................................................................................................................14
References.............................................................................................................................15
2

Problem scenario
The information on the database design for the project is included in this section of the report.
The current system suffers from a series of drawbacks due to manual nature of operations that
are involved. It leads to the problems around data duplication, slow speed for processing,
security attacks and risks.
In the database design, three elements are included as Group, Solo Performer and Artist. Artist
can be included as a Group as well as a Solo Performer. An integrated unit of Solo Performers is
referred as a Group. If a Solo Performer is a part of a certain group, then it is referred as the
group’s member. These performers can be associated with one to many instruments. There will
be numerous benefits that will be offered by the automated system, such as organized
information storage and allocation of space. The security of the information and the ability to
prevent and avoid the security attacks will also improve. Further information regarding the
database will be available in the tables which will appear as forms in the front end.
Conceptual design of proposed system
Enhanced Entity Relationship Diagram (EERD)
Business rules
Following are some of the key rules for business that are associated with the system design that
has been proposed:
 An artist could only be a group or solo-performer, however they cannot be both
 A solo-performer can be part of multiple groups
 A group can also include more than a single solo-performer
 A solo-performer is allowed to play more than one instrument, however a single
instrument could only be played by a single solo-performer at any given time
 Artists name would always be unique
 Instruments name would always be unique
 Name of the owners of the concert would always be unique
 A concert can have one owner but an owner can have multiple concerts
 There cannot be a single solo-performer in the concert
 A concert is allowed to organize just one gig
3

Logical database design
Normalized tables
4

Database design in Oracle
CREATE TABLE Artist
(
aNo NUMBER(9) NOT NULL,
aName CHAR(30) NOT NULL,
aType CHAR(1) NOT NULL,
rDate DATE NOT NULL,
CONSTRAINT PK_art PRIMARY KEY (aNo),
CONSTRAINT UK_Ar_Name UNIQUE (aName),
5

CONSTRAINT CK_Art_Type CHECK (aType IN ('G', 'S'))
);
CREATE TABLE TGroup
(
grpID NUMBER(9) NOT NULL, -- PK and FK.
creationDate DATE NOT NULL,
CONSTRAINT PK_Grp PRIMARY KEY (grpID),
CONSTRAINT FK_Group_Artist FOREIGN KEY (grpID) REFERENCES Artist (aNo)
);
CREATE TABLE SoloPerformer
(
soloPerfID NUMBER(9) NOT NULL, -- PK and FK.
dob DATE NOT NULL,
CONSTRAINT PK_Sol PRIMARY KEY (soloPerfID),
CONSTRAINT FK_SolPerformerNo_to_Artist FOREIGN KEY (soloPerfID) REFERENCES
Artist (aNo)
);
CREATE TABLE GroupMember
(
mID NUMBER(9) NOT NULL,
gID NUMBER(9) NOT NULL,
joinDate DATE NOT NULL,
CONSTRAINT PK_GrpMember PRIMARY KEY (mID, gID),
CONSTRAINT FK_Member FOREIGN KEY (mID) REFERENCES SoloPerformer
(soloPerfID),
CONSTRAINT FK_from_GrpMem_to_MyGroup FOREIGN KEY (gID) REFERENCES
TGroup(grpID)
);
CREATE TABLE Instrument
(
instNo NUMBER(9) NOT NULL,
iName CHAR(30) NOT NULL,
CONSTRAINT PK_Instrument PRIMARY KEY (instNo),
CONSTRAINT UK_Instrument_Name UNIQUE (iName) -- UNIQUE KEY.
);
CREATE TABLE SoloPerformerInstrument
(
soloID NUMBER(9) NOT NULL,
instruNo NUMBER(9) NOT NULL,
createdDate DATE NOT NULL,
6

CONSTRAINT PK_SoloPerfInstrument PRIMARY KEY (soloID, instruNo), -- Composite
PK.
CONSTRAINT FK_Solo_Perf FOREIGN KEY (soloID) REFERENCES SoloPerformer
(soloPerfID),
CONSTRAINT FK_Solo_instru FOREIGN KEY (instruNo) REFERENCES Instrument
(instNo )
);
CREATE TABLE Owner
(
oID NUMBER(9) NOT NULL,
ownerName CHAR(30) NOT NULL,
bPhone NUMBER(5),
CONSTRAINT PK_Own PRIMARY KEY (oID),
CONSTRAINT UK_Ow_Name UNIQUE (ownerName) -- UNIQUE KEY.
);
CREATE TABLE Concert
(
cID NUMBER(9) NOT NULL,
oID NUMBER(9) NOT NULL,
oName CHAR(30) NOT NULL,
CONSTRAINT PK_Concert PRIMARY KEY (cID), -- PK.
CONSTRAINT FK_Owner FOREIGN KEY (oID) REFERENCES Owner (oID),
CONSTRAINT UK_Concert_Name UNIQUE (oName) -- UNIQUE KEY.
);
CREATE TABLE Gig
(
cID NUMBER(9) NOT NULL,
gID NUMBER(9) NOT NULL,
gDate DATE NULL,
CONSTRAINT PK_Gig PRIMARY KEY (cID, gID), -- PK.
CONSTRAINT FK_Gig_concert FOREIGN KEY (cID) REFERENCES Concert (cID),
CONSTRAINT FK_Gig_group FOREIGN KEY (gID) REFERENCES TGroup (grpID)
);
Database indexes
There is a primary key that is present in all the tables which can be obtained from the logical
database designed. There are unique indexes that are created and are as listed below.
 Unique artist name – This is the index to make sure that the artists do not have the same
name. An artist can either be a part of a group or may also be a solo-performer. The
identification of the artist will be troublesome in case of same name.
 Unique instrument name – An artist can play one instrument or more than one
instrument. The instrument name shall therefore be different to avoid confusion.
7

 Unique concert name – The concerts shall have different names so that the identification
of the events and concerts is not difficult.
 Unique owner name – Sharing of the owner name will lead to confusions for the
identification of the owner for the end-users. This is the index that is applied and created
to avoid such confusions.
Data population
8

SQL Query
Query 1: select aNo as Artisit_ID, aName as Artist_Name from Artist order by aNo;
9

Query 2: select Artist.aNo, TGroup.grpID from Artist FULL OUTER JOIN TGroup ON
Artist.aNo=TGroup.grpID;
10

Query 3: select a.aNo, a.aName, g.grpID as TGroup from Artist a, TGroup g, GroupMember gm,
SoloPerformer so where g.grpID = gm.gID and gm.mID = so.soloPerfID and so.soloPerfID
=a.aNo and g.grpID = 4;
Query 4: select cID, count(gID) from Gig group by cID;
11

Query 5: select gID, count(mID) from GroupMember group by gID order by count(mID) desc;
Query 6: select a.aName, i.iName from Artist a, Instrument i, SoloPerformerInstrument si where
a.aNo = si.soloID and si.instruNo = i.instNo and a.aName Like '%M%';
12