Notown Records Database- Data to future Music requirement

Introduction

Notown records has decided to store information about musicians who perform on its albums(as well as other company data) in a database. In this project, we have designed the conceptual schema for notown and ER diagram based on following requirements.

a)     Each musician that records at Notown has an SSN, a name, an address and a phone No.they may have one phone No.

b)    Each instruments used in songs recorded at Notown has a name(e.g. guitar, Synthesizer and flute) and a musical key(e.g. C,B-flat,E-flat)

c)     Each album recorded on the Notown label has a title, a copyright date, a format(e.g.  CD or MC), and a album identifier.

d)    Each song recorded at Notown has a title and an author.

e)     Each musician may play sevaralinstruments, and a given instruments may be played by sevaral musucians.

f)     Each album has a number of songs on it, but no song may appear on more than one album.

g)    Each song is peromed by one or more musicians, and a musician may perform a number of songs.

h)    Each album has exactly one musician who ascts as its producer. A musician may produce several albums.

i)      Producer may perform more than one recording of event of songs.

j)      Separate gorup of musucians may release the album, and more than one musicians can be part of muitlple release group.

k)    Release group is responsible for the final release .

l)      Few musicians make the group for releasing album and remain musicians may perform the no of songs.

The company has the major part that are performing songs and releasing the album to their customer. So to introduce the better prpducts, they have the many instruments to cater the requirments. By using their own capabilities, they are creating songs with music notes that are recording in a database also.

Source : ("Database design basics (Microsoft Access 2007)")

Goal of the project

We mainly target the design of database structure to achieve the automated system to analyze the tracking of songs and albums that are releasing, amount of releasing of songs, weekly Album reviews and daily recording progress. By using real time search facility, the system can manipulate the records. So the system may store the each functionality of the recording.

We have to design a database to collect these large volumes of data which is happening in manual system for better decisions and analyze in future to be achieved the goal.

·       To store the album and its related song details

·       To understand latest strength of music trends

·       To understand customer desired level on music

·       To understand the competition of other recording lab

·       To be solved the weakness of process

·       Tracking Customer reviews

Source : (Paul Litwin)

Design Solution

Relationship diagram

  

Implementation

Create Tables

-- Database: 'notown'

-- Table structure for table 'album'

CREATE TABLE IF NOT EXISTS 'album' (

  'AlbumID' varchar(10) NOT NULL,

  'Title' varchar(150) NOT NULL,

  'CopyrightDate' date NOT NULL,

  'Format' varchar(10) NOT NULL,

  'length' time NOT NULL,

  'SSN' int(11) NOT NULL,

  'comment' varchar(100) NOT NULL,

  PRIMARY KEY ('AlbumID')

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table structure for table 'group'

CREATE TABLE IF NOT EXISTS 'group' (

  'GroupID' varchar(10) NOT NULL,

  'Name' varchar(150) NOT NULL,

  PRIMARY KEY ('GroupID')

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table structure for table 'group_perform_song'

CREATE TABLE IF NOT EXISTS 'group_perform_song' (

  'GroupID' varchar(10) NOT NULL,

  'SongID' varchar(10) NOT NULL,

  'Date' date NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table structure for table 'group_release_album'

CREATE TABLE IF NOT EXISTS 'group_release_album' (

  'AlbumID' varchar(10) NOT NULL,

  'groupID' varchar(10) NOT NULL,

  'date' date NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table structure for table 'instruments'

CREATE TABLE IF NOT EXISTS 'instruments' (

  'Name' varchar(150) NOT NULL,

  'MusicalKey' varchar(250) NOT NULL,

  PRIMARY KEY ('Name')

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table structure for table 'musician'

CREATE TABLE IF NOT EXISTS 'musician' (

  'SSN' int(11) NOT NULL,

  'Name' varchar(150) NOT NULL,

  'Address' varchar(250) NOT NULL,

  'Contact' varchar(10) NOT NULL,

  PRIMARY KEY ('SSN')

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table structure for table 'musician_contact'

CREATE TABLE IF NOT EXISTS 'musician_contact' (

  'SSN' int(11) NOT NULL,

  'Contact 1' varchar(10) NOT NULL,

  'Contact 2' varchar(10) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table structure for table 'musician_join_group'

CREATE TABLE IF NOT EXISTS 'musician_join_group' (

  'GroupID' varchar(10) NOT NULL,

  'SSN' int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table structure for table 'musician_play_instrument'

CREATE TABLE IF NOT EXISTS 'musician_play_instrument' (

  'SSN' int(11) NOT NULL,

  'Name' varchar(150) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table structure for table 'producer'

CREATE TABLE IF NOT EXISTS 'producer' (

  'SSN' int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table structure for table 'producer_record_song'

CREATE TABLE IF NOT EXISTS 'producer_record_song' (

  'SSN' int(11) NOT NULL,

  'SongID' varchar(10) NOT NULL,

  'date' date NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table structure for table 'release_group'

CREATE TABLE IF NOT EXISTS 'release_group' (

  'GroupID' varchar(10) NOT NULL,

  'Type' varchar(25) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table structure for table 'songs'

REATE TABLE IF NOT EXISTS 'songs' (

  'SongID' varchar(10) NOT NULL,

  'Title' varchar(150) NOT NULL,

  'Author' varchar(100) NOT NULL,

  PRIMARY KEY ('SongID')

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE ' release_group '

  ADD CONSTRAINT ' groupid _fk' FOREIGN KEY (' groupid ') REFERENCES 'group' (' groupid ');

ALTER TABLE ' group_ release_album '

  ADD CONSTRAINT ' groupid_fk' FOREIGN KEY (' groupid ') REFERENCES 'group (' groupid ');

ALTER TABLE ' group_ release_album '

  ADD CONSTRAINT ' albumid_fk' FOREIGN KEY (' albumid ') REFERENCES 'album' (' albumid ');

ALTER TABLE ' musician_join_group'

  ADD CONSTRAINT ' groupid_fk' FOREIGN KEY (' groupid ') REFERENCES 'group' (' groupid ');

ALTER TABLE ' producer_record_song'

  ADD CONSTRAINT ' songid_fk' FOREIGN KEY (' songid ') REFERENCES 'song' (' songid ');

ALTER TABLE ' producer_record_song'

  ADD CONSTRAINT ' ssn_fk' FOREIGN KEY (' ssn ') REFERENCES 'producer' ('ssn ');

ALTER TABLE ' group_perform_song'

  ADD CONSTRAINT ' songid_fk1' FOREIGN KEY (' songid ') REFERENCES 'song' (' songid ');

ALTER TABLE ' group_perform_song''

  ADD CONSTRAINT ' groupid_fk1' FOREIGN KEY (' groupid ') REFERENCES 'group' (' groupid ');

ALTER TABLE ' musician_contact’

  ADD CONSTRAINT ' ssn_fk1' FOREIGN KEY (' ssn') REFERENCES 'musician' (' ssn');

ALTER TABLE ' musician_play_instrument’

  ADD CONSTRAINT ' ssn_fk2' FOREIGN KEY (' ssn') REFERENCES 'musician' (' ssn');

Data Entering

 

-- Dumping data for table 'group'

INSERT INTO 'group' ('GroupID', 'Name') VALUES

('G001', 'nelum'),

('G002', 'Olu'),

('G003', 'Manel'),

('G004', 'Pichcha'),

('G005', 'Saman');

-- Dumping data for table 'group_perform_song'

INSERT INTO 'group_perform_song' ('GroupID', 'SongID', 'Date') VALUES

('G001', 'S001', '2018-01-15'),

('G002', 'S002', '2018-01-03'),

('G003', 'S003', '2018-01-07'),

('G004', 'S004', '2018-01-12'),

('G005', 'S005', '2018-01-04');

-- Dumping data for table 'group_release_album'

INSERT INTO 'group_release_album' ('AlbumID', 'groupID', 'date') VALUES

('A001', 'G001', '2018-01-24'),

('A002', 'G002', '2018-01-31'),

('A003', 'G003', '2018-01-28'),

('A004', 'g004', '2018-01-29'),

('A005', 'G005', '2018-01-25');

-- Dumping data for table 'instruments'

INSERT INTO 'instruments' ('Name', 'MusicalKey') VALUES

('Box Guitar', 'A'),

('Flute', 'E-Flat'),

('Guitar', 'C-Flat'),

('Organ', 'F-Flat'),

('Sithar', 'F'),

('Violin', 'D');

-- Dumping data for table 'musician'

INSERT INTO 'musician' ('SSN', 'Name', 'Address', 'Contact') VALUES

(1201, 'sumith', 'apura', ''),

(1202, 'bandara', 'ratnapura', ''),

(1203, 'kumara', 'matale', ''),

(1204, 'sandali', 'colombo', ''),

(1205, 'saduni', 'gampaha', '');

-- Dumping data for table 'musician_contact'

INSERT INTO 'musician_contact' ('SSN', 'Contact 1', 'Contact 2') VALUES

(1201, '0774551122', '0713233333'),

(1202, '0754444122', '0714854452'),

(1203, '0725535512', '0715545525'),

(1204, '0714444555', '0774555211'),

(1205, '0716666651', '0778855451');

-- Dumping data for table 'album'

INSERT INTO 'album' ('AlbumID', 'Title', 'CopyrightDate', 'Format', 'length', 'SSN', 'comment') VALUES

('A001', 'Sada Tharuwa', '2018-01-16', 'wav', '45:00:00', 1201, 'good suond'),

('A002', 'hada paluwa', '2018-01-01', 'wav', '48:00:00', 1202, 'best'),

('A003', 'thaniwee', '2018-01-05', 'mp3', '02:50:00', 1203, 'better'),

('A004', 'Nihada raa', '2018-01-04', 'wav', '90:00:00', 1204, 'high gain'),

('A005', 'obage pema', '2018-01-14', 'mp3', '02:45:20', 1205, 'bad');

-- Dumping data for table 'songs'

INSERT INTO 'songs' ('SongID', 'Title', 'Author') VALUES

('S001', 'madhu mala', 'milton perera'),

('S002', 'obe nil nuwan', 'milton perera'),

('S003', 'obe kadulu', 'neela wicramasinghe'),

('S004', 'ahal mal', 'chandana liyanaarchchi'),

('S005', 'parami dam', 'neela wicramasinghe');

-- Dumping data for table 'release_group'

INSERT INTO 'release_group' ('GroupID', 'Type') VALUES

('G001', 'RELEASE GROUP'),

('G002', 'OTHER GROUP'),

('G004', 'RELEASE GROUP'),

('G003', 'OTHER GROUP'),

('G005', 'RELEASE GROUP');

-- Dumping data for table 'producer_record_song'

INSERT INTO 'producer_record_song' ('SSN', 'SongID', 'date') VALUES

(1201, 'S001', '2017-12-12'),

(1202, 'S002', '2017-12-04'),

(1203, 'S003', '2017-12-18'),

(1204, 'S004', '2017-12-18'),

(1205, 'S005', '2017-11-28');

-- Dumping data for table 'producer'

INSERT INTO 'producer' ('SSN') VALUES

(1201),

(1202),

(1203),

(1204),

(1205);

-- Dumping data for table 'musician_play_instrument'

INSERT INTO 'musician_play_instrument' ('SSN', 'Name') VALUES

(1201, 'Box Guitar'),

(1202, 'Flute'),

(1203, 'Violin'),

(1204, 'organ'),

(1205, 'Box Guitar');

-- Dumping data for table 'musician_join_group'

INSERT INTO 'musician_join_group' ('GroupID', 'SSN') VALUES

('G001', 1201),

('G002', 1202),

('G003', 1203),

('G004', 1204),

('G005', 1205);

  

Tools and methodologies used

The tools of the SQL Transaction language is used here for building the database structure and can connect to external program using ADO.net Adapter of OLEDB Data Base system.  As The SQL editors, we used MySQL editor and various SQL Software such as Microsoft Access 2010, Microsoft SQL Express 2008.

The relational database management system is heavily used in this project and with proper relations between entities is normalized to extract correct data flows by external applications. To maintain the correct data in entities or tables, we used constraints and data type properties in attributes which was described the entity for accuracy of data.

Lesson Learned

When I was preparing my project report, I have got more things about the database system and mentioned few things as mentioned below. From the web, I studied similar project and database schema on marketing companies.

               i.         Conceptual and physical database methods

             ii.         Normalization

            iii.         Relationship of Database Tables

            iv.         Various techniques on querying the database

              v.         Design of database relations and their property setup

            vi.         SQL Query language tools and RDBMS process

Conclusion

The fast moving recording company in Sri Lanka had a huge volume of data to store and need the management information accordingly with short time for better analyzing of future aspects of listener’s feelings or to forecast the area of music. Therefore the database system is an essential requirement in such situation. So the design of database system is helpful to any front end software to communicate with the end user for the any purpose of data analyzing part. To get more fan for the albums in Notown Company, they need review tracking system to better output to it’s customer.