Practicale Assignment for Sage 50

Create a Chart of Accounts for North Traders (pvt) Ltd. And apply the suitable number system with last digit. Keep account balance zero and generate a account report for this COA. 2 Hr

Asset Accounts

No.	Account Title	To Increase	Description/Explanation of Account
101	Cash	Debit	Checking account balance (as shown in company records), currency, coins, checks received from customers but not yet deposited.
120	Accounts Receivable	Debit	Amounts owed to the company for services performed or products sold but not yet paid for.
140	Merchandise Inventory	Debit	Cost of merchandise purchased but has not yet been sold.
150	Supplies	Debit	Cost of supplies that have not yet been used. Supplies that have been used are recorded in Supplies Expense.
160	Prepaid Insurance	Debit	Cost of insurance that is paid in advance and includes a future accounting period.
170	Land	Debit	Cost to acquire and prepare land for use by the company.
175	Buildings	Debit	Cost to purchase or construct buildings for use by the company.
178	Accumulated Depreciation - Buildings	Credit	Amount of the buildings' cost that has been allocated to Depreciation Expense since the time the building was acquired.
180	Equipment	Debit	Cost to acquire and prepare equipment for use by the company.
188	Accumulated Depreciation - Equipment	Credit	Amount of equipment's cost that has been allocated to Depreciation Expense since the time the equipment was acquired.

Liability Accounts

No.	Account Title	To Increase	Description/Explanation of Account
210	Notes Payable	Credit	The amount of principal due on a formal written promise to pay. Loans from banks are included in this account.
215	Accounts Payable	Credit	Amount owed to suppliers who provided goods and services to the company but did not require immediate payment in cash.
220	Wages Payable	Credit	Amount owed to employees for hours worked but not yet paid.
230	Interest Payable	Credit	Amount owed for interest on Notes Payable up until the date of the balance sheet. This is computed by multiplying the amount of the note times the effective interest rate times the time period.
240	Unearned Revenues	Credit	Amounts received in advance of delivering goods or providing services. When the goods are delivered or services are provided, this liability amount decreases.
250	Mortgage Loan Payable	Credit	A formal loan that involves a lien on real estate until the loan is repaid.

 Owner's Equity Accounts

No.	Account Title	To Increase	Description/Explanation of Account
290	Mary Smith, Capital	Credit	Amount the owner invested in the company (through cash or other assets) plus earnings of the company not withdrawn by the owner.
295	Mary Smith, Drawing	Debit	Amount that the owner of the sole proprietorship has withdrawn for personal use during the current accounting year. At the end of the year, the amount in this account will be transferred into Mary Smith, Capital (account 290).

 Operating Revenue Accounts

No.	Account Title	To Increase	Description/Explanation of Account
310	Service Revenues	Credit	Amounts earned from providing services to clients, either for cash or on credit. When a service is provided on credit, both this account and Accounts Receivable will increase. When a service is provided for immediate cash, both this account and Cash will increase.

 Operating Expense Accounts

No.	Account Title	To Increase	Description/Explanation of Account
500	Salaries Expense	Debit	Expenses incurred for the work performed by salaried employees during the accounting period. These employees normally receive a fixed amount on a weekly, monthly, or annual basis.
510	Wages Expense	Debit	Expenses incurred for the work performed by non-salaried employees during the accounting period. These employees receive an hourly rate of pay.
540	Supplies Expense	Debit	Cost of supplies used up during the accounting period.
560	Rent Expense	Debit	Cost of occupying rented facilities during the accounting period.
570	Utilities Expense	Debit	Costs for electricity, heat, water, and sewer that were used during the accounting period.
576	Telephone Expense	Debit	Cost of telephone used during the current accounting period.
610	Advertising Expense	Debit	Costs incurred by the company during the accounting period for ads, promotions, and other selling and expenses (other than salaries).
750	Depreciation Expense	Debit	Cost of long-term assets allocated to expense during the current accounting period.

 Non-Operating Revenues and Expenses, Gains, and Losses

No.	Account Title	To Increase	Description/Explanation of Account
810	Interest Revenues	Credit	Interest and dividends earned on bank accounts, investments or notes receivable. This account is increased when the interest is earned and either Cash or Interest Receivable is also increased.
910	Gain on Sale of Assets	Credit	Occurs when the company sells one of its assets (other than inventory) for more than the asset's book value.
960	Loss on Sale of Assets	Debit	Occurs when the company sells one of its assets (other than inventory) for less than the asset's book value.

Account Balance need to enter to the system and generate balance sheet.

Cash                                                                 10000

Accounts Receivable                                          50000

Merchandise Inventory                                       20000

Supplies                                                             20000

Prepaid Insurance                                              30000

Land                                                                  100000

Buildings                                                             100000

Accumulated Depreciation – Buildings                10000

Equipment                                                          150000

Accumulated Depreciation – Equipment              60000

    Sub Total                                                                      550000

Notes Payable                                   5000

Accounts Payable                              5000

Wages Payable                                 20000

Interest Payable                                5000

Unearned Revenues                         10000

Mortgage Loan Payable                   35000

                            Sub Total                                            80000

Mary Smith, Capital                        500000

Service Revenues                                                            70000                                                                                                                                                                   

Salaries Expense                              100000 

Wages Expense                                50000

Supplies Expense                             5000

Rent Expense                                   5000

Utilities Expense                               5000

Telephone Expense                          5000

Advertising Expense                        10000

Depreciation Expense                      120000

                                    Sub Total                             300000

Interest Revenues

Gain on Sale of Assets                   150000

Loss on Sale of Assets                    50000

                                          Sub Total                         200000

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.