Alan Counting, Edward Quals and Peter Rofit are in partnership in an accounting practice, CQR. The practice specialises in taxation, auditing and financial advice for small to medium sized clients. They started the practice in 2005 and it has grown to now employ 25 people. These employees include other accountants and office staff. Alan, Edward and Peter believe the practice is headed for a period of significant growth and have come to you to help them redevelop their Client Billing (CB) system so that it copes better with their workflows and supports future growth opportunities.

At the moment, when a client turns up for a scheduled appointment or when an accountant visits a client, the client's information is referenced using the client's name. If it is the first contact between the practice and client, the client is asked to complete their details (i.e. name, address, contact name, contact number, ABN, TFN, structure, sector, service) on a client information form. Some of this information is optional based on the type of client and area they operate in. For example, some clients are simply salary and wage earners looking for assistance to complete tax returns and would not have an ABN, whilst others are companies requiring advice and assistance with the many different aspects of operating their business. Once the relevant information is obtained for a new client, a unique client number is allocated to the client.

The client is the legal entity being dealt with by the practice and clients are created based on their business structure type. A client record can only have one structure type. Examples include sole trader, individual, private company, partnership and trust. If for example, two individuals operate a business as a private company, and they want their company's tax return completed as well as their own, a client record would be created for that private company and separate client records would be set up to deal with the affairs of each of the individuals. There are circumstances though where a legal entity sits within another but this is not obvious e.g. where the practice performs superannuation duties and compiles returns for a self-managed superannuation fund for an individual. In these circumstances, there are two client records - the individual and the superannuation fund.

Sectors are the broad categories that allow grouping of clients for reporting purposes. Examples include retail, construction, financial services, hospitality and manufacturing. Occasionally new sector categories are created and the practice would like to record these with their code and description in a CRICOS Provider No. 00103D ITECH1006/5006 Database Management Systems - Assignment 1 Specification Sem3 2014 (201427) Vn. 2 Page 2 of 7 separate table. They would also like to be able to record that a client may operate in none, one or more sectors. Services are the broad categories that the practice offers. Examples include auditing, taxation, financial planning, corporate advice and superannuation administration. The practice would like to be able to record that a client must use at least one service but may use a number of services.

The practice has a separate Human Resources (HR) system recording all employee details but for the purposes of billing, they record specific billing details about an employee within the CB system. This information is recorded based on the unique empAssignment Submission You should produce a single pdf file, created using Word, containing all of the requirements as specified within this document. Your lecturer will provide details of how the file is to be submitted

The design document should contain:

1. A completed copy of the SITE Assignment Coversheet.

2. An appropriate title page that includes an acknowledgement of all students you have spoken to about the assignment.

3. A table of contents and automatically generated page numbers.

4. An entity relation (E-R) diagram that models the problem which includes:

a. all entities, relationships (including names) and attributes;

b. primary (underlined) and foreign (italic) keys identified;

c. cardinality and participation (optional / mandatory) symbols; and

d. assumptions you have made, e.g. how you arrived at the cardinality/participation for those not mentioned or clear in the business description, etc.

The E-R should be completed using the standards of this course (crow's feet).

5. Relational data structures that translate your E-R diagram which includes:

a. relation (table) names,

b. attribute (column ) names and field types (as required by WAMP),

c. primary and foreign keys identified;

The data structures should be shown using the standards of this course.

6. Normalisation of relations which identifies:

a. dependency diagram for each relation

b. the level of Normalisation achieved for each relation c. the reasons for any relation that is maintained NOT in 3NF.

7. Relational schema that:

a. correctly translates the E-R submitted; and

b. has appropriate tables, columns, primary and foreign keys.

8. A bibliography, in APA format, containing all resources used to complete the assignment. If no resources have been used please indicate this appropriately.

Your assignment should be completed according to the University of Ballarat General Guide