Assessment – Project

Instructions to Students: Please read the following instructions carefully.

• The purpose of this assessment is to assess your knowledge and skills.
  
  
• This assessment is to be completed according to the instructions.
  
  
• If you are not sure about any aspect of this assessment, please ask for clarification from your assessor.  
  
  
• If you have questions and other concerns that may affect your performance in the assessment, please inform the assessor immediately.
  
  
• To be deemed satisfactory in this assessment, you must write the answers to all written questions correctly or meet all the performance criteria for activity based assessments.
  
  
• You will get a due date and submission after the due date will result in a penalty fee.
  
  
• Should you not complete the tasks correctly, you will be given feedback on the results.
  
  
• You are entitled to one (1) resubmission of this task.
  
  
• If you feel the decision made by your assessor was incorrect please refer to your Student Handbook for information on assessment appeal.
  
  
• Please refer to the College re-submission and re-sit policy for more information.

This is individual Assessment. Hand in or submit the hard copy of the Assessments to your Trainer/ Assessor.

This assessment task requires you to create simple two-table relational databases with reports and queries for storage and retrieval of information considering the organisational requirements given in the case study.

When creating relational databases, you are required to:

• Comply with organisational requirements when inputting, amending and storing data, including correct naming conventions
  
  
• Strictly adhere to the task specifications, including:
  
  
  • following designated timelines
    
    
  • achieving speed and accuracy
    
    
• Create simple databases, including reports and queries
  
  
• Follow designated timelines when generating reports for the queries.
  
  
• Correctly name and store databases
  
  
• Distribute reports for the queries to Supervisor.
  
  

During this assessment task, you will be required to complete the following activities:

• Activity 1: Create a simple database
  
  
• Activity 2: Create reports and queries and use the database
  
  
• Activity 3: Distribute reports and database to Supervisor
  
  

‘India at your house’ is a wholesale distributor of the following Indian products in Australia.

• Nanak Ghee
  
  
• Lays chips
  
  
• Good day Biscuit   
  
  
• Crunchy Munch
  
  
• Steam Rice
  
  
• Britannia Cakes
  
  
• Salt n Sweet Crux

The major customers of the organisation are:

• Spice India
  
  
• Nanak groceries  
  
  
• Vikas Foods
  
  
• Singh groceries
  
  
• India at Home

‘India at your house’ has established two (2) tables that detail the food items, their respective codes and company details.

These tables are as below:

ITEM_ID	ITEM_NAME   Â	ITEM_UNIT	COMPANY_ID
1	Nanak Ghee	Pcs	16
6	Lays chips	Pcs	15
2	Good day Biscuit  Â	Pcs	15
3	Crunchy Munch	Pcs	17
4	Steam Rice	Pcs	15
5	Britannia Cakes	Pcs	18
7	Salt n Sweet Crux	Pcs	19

 

COMPANY_ID	COMPANY_NAME	COMPANY_CITY
18	Spice India	Melbourne
15	Nanak groceries Â	Sydney
16	Vikas Foods	Melbourne
15	Singh groceries	Sydney
19	India at Home	Melbourne

 

The organisation wants the database administrator of the organisation to create simple two-table relational databases with reports and queries for storage and retrieval of information.

The organisation has provided you with the following organisational requirements to be followed when creating databases inputting, amending and storing data:

Databases to be used

• SQL databases such as MySQL

Design principles

• Determine the purpose of your database
• Find and organise the information required     
• Divide the information into tables    
• Turn information items into columns    
• Specify primary keys    
• Set up the table relationships    
• Refine your design    
• Apply the normalisation rules

Inputting and amending data

• COMPANY_ID should be the primary key in the ‘company’ table,
• COMPANY_ID should be a foreign key in the ‘foods’ table which is referencing the primary key of COMPANY table,
• COMPANY_ID of ‘company’ and ‘foods’ must be the same.

Naming conventions for storing databases

Databases should be stored using the following conventions:

• COMPANY_ID should be the primary key in the ‘company’ table,
• COMPANY_ID should be a foreign key in the ‘foods’ table which is referencing the primary key of COMPANY table,
• COMPANY_ID of ‘company’ and ‘foods’ must be the same.

Designated timelines

Reports should be generated for queries within a period of 24 hours.

Distributing reports

Reports should be distributed to the Supervisor via e-mail.

Your roles and responsibilities:

You are working as a database administrator in ‘India at your house’. Your Supervisor wants you to create simple two-table relational databases with reports and queries for storage and retrieval of information.

To do so, you will have the following responsibilities:

• Create a simple database with at least two tables using a database application, fundamental design concepts, software functions, and simple formulae.
• Create a table with fields and attributes based on database utilisation, data considerations, and user requirements.
• For each table, create a primary key.
• As necessary, change the table layout and field attributes.
• Establish a link between the two tables.
• Review and correct data entered in compliance with organisational and task criteria.
• To meet job requirements, determine information output, database tables to be used, and report structure.
• Create data groupings, search, and sort criteria to suit task requirements.
• Run reports and queries to ensure that the findings and equations offer the necessary data.
• Make changes to reports to incorporate or exclude additional requirements.
• Ensure that data input adheres to the specified timescales and organisational standards for speed and accuracy.
• Use manuals, user documentation, and online help to solve database design and production issues.
• Preview, customise, and print database reports or forms in compliance with organisational and task requirements.
• Name and store databases in accordance with organisational regulations, then quit the application without data loss or harm.
• Prepare and disseminate reports in a proper format to the right person.

This activity requires you to create a simple database having the two (2) tables using MySQL:

• Table 1: Food items and purchase details
• Table 2: Company/Client details

To do so, you are required to:

• Follow the steps given in Checklist 1 provided.
• Place a tick mark against each step once completed.

Submit the required evidence to the trainer/assessor.

This activity requires you to create the following reports and queries and use a database:

• Queries and reports:
  • Use SQL queries for 'item_name' and 'item_unit' from 'foods' table and 'company_name' and 'company_city' form 'company' table.
  • Generate reports for the SQL queries mentioned above.

To do so, you are required to:

• Follow the steps given in Checklist 2 provided.
• Place a tick mark against each step once completed.
• Submit the required evidence to the trainer/assessor.

This activity requires you to distribute reports and databases prepared in Activity 2 to the Supervisor.  

To do so, you are required to:

• Follow the steps given in Checklist 3 provided.
• Place a tick mark against each step once completed.
• Submit the required evidence to the trainer/assessor.