Background information
Lalith and Sachit have been running a consulting firm- SL Consulting, in Sydney since 2005. Their customer base has reached to 100 and they have started employing casual consultants to help them with the jobs.
SLC's customer base can be classified into two groups; Organisations and Individuals. Organisations generally pay SLC on monthly basis. They call SLC whenever their services are required during the month and at the end of the month will receive an invoice with itemized service list. Individual customers will receive and pay the invoice right after the work is completed.
SLC offers six different services; strategy planning ($200), succession planning ($300), legal ($400), financial ($150), tax planning ($120), and dispute resolution ($100). Each service has a fixed hourly rate as shown in the brackets.
The casual employees of SLC, around 20 in number, are highly professionals such as, business analysts ($70), Content Writers ($35), counsellors ($90), auditors ($100), mediators ($55), lawyers ($120), and group /meeting facilitators ($60). The hourly rates for these jobs differ greatly as shown in the brackets.
For example, VETSOL, a client of SLC, is looking for Strategy planning. SLC will negotiate the number of hours with VETSOL. Let's say they agreed to complete the project in 300 hours. Then SLC will invoice VETSOL a total of $60K for the project (300 hours X $200 per hour).
SLC might employ their consultants to complete this project. Several tasks needs to be completed to complete this project. They might need to do business analysis (50 hours), document auditing (30 hours), attend meetings/ group discussions (60 hours), give presentations (30 hours), and prepare reports (100 hours). The reports have to go through the legal team (30 hours) before final submission. These individual consultants will submit their timesheets for the tasks performed during that pay period. SLC will calculate individual consultants pay based on the tasks performed as shown in Table 1;
|
|
|
|
|
|
|
Business analysis
|
50 hours
|
$ 70
|
$3,500
|
Lalith
|
|
Document Auditing
|
30 hours
|
$ 100
|
$3,000
|
Pam Smith
|
|
Meetings/ Presentations
|
90 hours
|
$ 60
|
$5,400
|
Sachit
|
|
Documentation
|
100 hours
|
$ 35
|
$3,500
|
Jenny Wang
|
|
Legal
|
30 Hours
|
$ 120
|
$3,600
|
Tim Moore
|
SLC has been keeping track of their customers, projects and relevant invoices, and their casual employees and their positions, and the timesheet information using Excel Spreadsheets and some paper files.
You and your partner being apprentices at SLC have been delegated to find a solution and make this record keeping easy. You have decided to develop an Access database with the following functions;
• Add an employee. ( Explained in this write-up)
• Add a customer. (Challenge Task)
• Add a project. (Challenge Task)
• Enter a Timesheet. ( Explained in this write-up)
• Generate a Pay slip. ( Explained in this write-up)
• Generate an Invoice. (Challenge Task)
Database specification and requirements
The assignment to be undertaken involves:
1. Designing and creating the following basic (master) tables for the application:
• "Employee" table, to hold the details of employees.
• "Customer" table, to hold the details of customers. Also to keep the database in third normal form we will create another table- "customerType".
• "Position" table to hold the details of organisation specific roles and their hourly wage.
• "Service" table to hold the details of the services provided by SLC.
2. Designing and creating the following transactional tables for the application:
• "TimeSheet" table-that holds employee timesheet information.
• "Project" table - that holds customer projects.
3. Creating three forms; UpdateCustomer, UpdateEmployee, and UpdateProject, to update data in respective tables.
4. Improving the above mentioned forms, when the basic forms are working.
5. Creating a form - "Employee Time Sheet form" - which is used by the employees to enter their timesheet.
6. Improving the "Employee Time Sheet form" when the basic system is working.
7. Designing "Access Queries" to extract information from the data as required.
8. Creating a form - "Pay Slip", with a sub-form, to be issued to employees every fortnight.
9. Creating a form - "Customer Invoice", with a sub-form, to be issued to the clients after the work is done or at the end of the invoice period.
10. Creating a form - "Navigation Form" that will guide the stakeholders (owners and employees) in using this application.
In addition to preparing the Sachit & Lalith Consulting services Information System (SLCIS), you are required to prepare a write-up of around 1,500- 2,000 words explaining:
• The principles of database design, as demonstrated by the database design for this project. Research some introductory database design material on the web (use the online tutorials link in page 2) and refer to it in your report; in particular, show that you understand what a primary key is, what a foreign key is, what a datatype is, also what an autonumber is and how these are used in a database.
• The concept of normalisation, as done in the database by creating various tables and relationships between them. Explain the need for five (5) basic tables instead of four (4), i.e. why do we need a customer type table?
• Explain how you completed the "Challenge tasks". You can use this write-up as an example explanation. Include some screen shots of your final forms in your write-up.
• Mention the problems you experienced with this assignment (you will experience plenty) and explain how you got around them.
Your report needs to look like a business report with sections including executive summary, table of contents, list of figures, and references.
Table Creation
a. 7 tables created
b. Fields defined
c. Data types defined
d. Field descriptions added
e. Field properties set
Relationships
a. Relationship between table Customer and table Customer Type
b. Relationship between table Project and table Customer
c. Relationship between table Project and table Service
d. Relationship between table TimeSheet and table Project
e. Relationship between table TimeSheet and table Employee
f. Relationship between table Employee and table Position
Data entry-Appropriate and accurate data entered
a. 10 records in each table - Employee, Customer, Projects, and TimeSheet.
b. 2 records in the table Customer Type.
c. 6 records in the table Service.
d. 7 records in the table Position.
Basic forms created.
a. Update Employee form created.
b. Update Customer form created.
c. Update Project form created.
Time Sheet data entry form created and formatted
a. Master form created
b. Sub-form created
c. Forms combined
d. Combined form updated to meet the requirements